Transcript [email protected] SQLBI.COM DAX uses a syntax similar to Excel TableX [ColY] or [ColY] (fully qualified vs.

[email protected]
SQLBI.COM
DAX uses a syntax similar to Excel
TableX [ColY] or [ColY] (fully qualified vs. not qualified)
Cells cannot be referenced (like B23, B12:C15, …)
DAX functions operate with table columns
DAX Sample
Description
= [First Name] & “ “ & [Last Name]
String concatenation (like Excel)
= SUM(Sales[Amount])
SUM requires a column name, not a cell range
= RELATED (Product[Cost])
RELATED follows table relationships
Using DAX
Calculated Columns
Using DAX
Measures (or Calculated Field)
Sales Table
•
•
•
•
•
[Amount]
[Unit Cost]
[Total Cost]
[Margin]
MarginPct
= [Qty] * [Price]
= RELATED (Product [Cost])
= [Qty] * [Unit Cost]
= [Amount] – [Total Cost]
= [Margin] / [Amount]
Customer table
• [Sales] = SUMX (RELATEDTABLE(Sales), Sales[Amount])
Implicit Measures
• Automatically defined for all numeric columns
• Created measure “Sum of <field>”
• Can be changed to Avg, Count, Min, Max
Explicit DAX Measures
• Gives a name to a DAX expression (formula)
• Can use complex expressions
• Interaction with Filter Context
• [Margin %] = SUM( [Margin] ) / SUM( [Amount] )
Row Context
• Similar to current row concept
• i.e. row of calculation for calculated column
• It gives a meaning to a column reference
• Evaluation of expression in aggregation function
Filter Context
• Set of filters that defines “active” rows for each table
• Filters on single columns or on table rows
• Can be manipulated by using CALCULATE
• Every cell in a pivot table defines a filter context
RELATED( table[column] )
• Follows relationship many-to-one
• Reads column value in the corresponding row of the related table
RELATEDTABLE( table )
• Follows relationship in both directions
• Returns a table containing corresponding rows in the related table
• Must be used as a parameter calling another function – i.e.:
SUMX ( RELATEDTABLE(Sales), Sales[Amount])
Date and Time
DATE
DATEVALUE
DAY
EDATE
EOMONTH
HOUR
MINUTE
MONTH
NOW
SECOND
TIME
TIMEVALUE
TODAY
WEEKDAY
WEEKNUM
YEAR
YEARFRAC
Information
ISBLANK
ISERROR
ISLOGICAL
ISNONTEXT
ISNUMBER
ISTEXT
Logical
AND
IF
IFERROR
NOT
OR
FALSE
TRUE
Math and Trig
ABS
CEILING, ISO.CEILING
EXP
FACT
FLOOR
INT
LN
LOG
LOG10
MOD
MROUND
PI
POWER
QUOTIENT
RAND
RANDBETWEEN
ROUND
ROUNDDOWN
ROUNDUP
SIGN
SQRT
SUM
SUMSQ
TRUNC
Statistical
AVERAGE
AVERAGEA
COUNT
COUNTA
COUNTBLANK
MAX
MAXA
MIN
MINA
Text
CHAR
CODE
CONCATENATE
EXACT
FIND
FIXED
LEFT
LEN
LOWER
MID
REPLACE
REPT
RIGHT
SEARCH
SUBSTITUTE
TRIM
UPPER
VALUE
Table Functions in DAX
Differences from Excel
Differences between Excel and PowerPivot
• Excel doesn’t have functions returning a table
• Calculated Columns and Measures must return scalar values
DAX includes functions returning a table
• Used as intermediate results passed as arg to other functions
• Often used for DAX aggregation functions (aggX)
Table Functions in DAX
Functions List
RELATEDTABLE (Table)
FILTER (Table, Condition)
• Rows of related table following the relationship
• Filter rows from Table according Condition (whenever that Boolean
expression evaluates to true)
DISTINCT (Column)
• Table made of a single column containing all the distinct (unique)
values of a table column
VALUES (Column)
• As DISTINCT, but also returns “unknown” (i.e. invalid relationships)
ALL (Table), ALL (Column)
• Returns all data ignoring filter context and avoiding duplicates
ALLEXCEPT (Table, Col1, Col2, ..)
• Returns rows from a table using filter context only for specified
columns
Functions that transform Filter Context
• CALCULATE
• ALL
• FILTER
•…
Syntax samples
• Regular syntax (SalesAmt have to be an explicit measure)
= [SalesAmt] / CALCULATE( [SalesAmt], ALL (Product) )
• Shortened syntax
=[SalesAmt] / [SalesAmt]( ALL(Product) )
Measures on Sales Table
• A measure can reference other explicit measures defined in DAX
• References to implicit measures require aggregation functions
[Sales]
= SUM ( Sales [Amount] )
[AllProd]
= CALCULATE ([Sales], ALL(Product) )
= [Sales] ( ALL(Product) )
[Cost]
[Margin]
[Margin%]
= SUMX (Sales, [Qty] * [UnitCost])
= [Sales] – [Cost]
= [Margin] / [Sales]
DAX at work
Weight Percent
ABC / Pareto
Time Period Comparison
Customers / Products (distinct count)
Many-to-many relationships
i = value / rif
Regular CALCULATE syntax
Shortened CALCULATE syntax
AmountQuotaCustomers =
SUM( Orders[Amount] )
/ CALCULATE( SUM( Orders[Amount] ),
ALL( Customers ) )
OrdersAmount = SUM(Orders[Amount] )
AmountQuotaProducts =
SUM( Orders[Amount] )
/ CALCULATE( SUM( Orders[Amount] ),
ALL( Products ) )
AmountQuotaCustomers =
OrdersAmount
/ OrdersAmount ( ALL( Customers ) )
AmountQuotaProducts =
OrdersAmount
/ OrdersAmount ( ALL( Products ) )
ABC calculation requires calculated column
• Required attributes for pivot table axies, not measures
Calculated Columns for ABC
• SalesAmount – Sales amount for product, customer, …
• Cumulated – Cumulated sales amount (descendent order)
• SortedWeigth – Cumulated Weight Percent (descendent order)
• ABC Class – A for 70%, B for 20%, C for 10%
ABC / Pareto
SalesAmountProduct
ABC / Pareto
CumulatedProduct
(
(
(
))
)
ABC / Pareto
SortedWeightProduct
ABC / Pareto
ABC Product
(
(
))
Time Period Comparison
Year-To-Date
Date Table
• Create a table with dates (like a Date dimension)
• Use contiguous dates (no missing days)
• Fields for all required time aggregations (year, month, …)
Year to Date – Cumulated value starting from first day of year
• DATESYTD changes filter context, TOTALYTD avoids CALCULATE
[Amount YTD] =
CALCULATE(
SUM( Orders[Amount] ),
DATESYTD( Dates[Date] ) )
[Amount YTD 2] =
TOTALYTD(
SUM( Orders[Amount] ),
Dates[Date] )
Time Period Comparison
Previous Year
Corresponding value on previous year
• Use DATEADD to change filter context (SAMEPERIODLASTYEAR as alternative)
AmountPrevYear =
AmountPrevYear2 =
CALCULATE(
CALCULATE(
SUM( Orders[Amount] ),
SUM( Orders[Amount] ),
DATEADD( Dates[Date], -1, YEAR ) )
SAMEPERIODLASTYEAR( Dates[Date] ) )
Total value on previous year
• Use PARALLELPERIOD to get complete period (year)
AmountTotPrevYear =
CALCULATE(
SUM( Orders[Amount] ),
PARALLELPERIOD( Dates[Date], -1, YEAR ) )
Time Period Comparison
Last 12 months
Moving Annual Total (cumulated value of last 12 months)
• Use DATESBETWEEN to change filter context
Amount 12M=
CALCULATE(
SUM( Orders[Amount] ),
DATESBETWEEN(
Dates[Date],
NEXTDAY(
SAMEPERIODLASTYEAR(
LASTDATE( Dates[Date] ) ) ),
LASTDATE( Dates[Date] ) ) )
Number of Unique Customers / Products
Distinct Count Measure
(
(
(
(
))
(
(
(
)
(
))
)))
http://sqlblog.com/blogs/marco_russo/archive/2009/12/07/many-to-manyrelationships-in-powerpivot.aspx
DAX syntax similar to Excel formulas
• Calculated Columns (PowerPivot tables)
• Measures (Excel pivot tables)
Features available in DAX
•
•
•
•
•
•
Excel functions (math, statistical, date/time, text, etc.)
Aggregation of any expression over a table
Table functions (FILTER, RELATEDTABLE, DISTINCT, ecc.)
Fetch data across relationships (one-many, many-one)
Set or modify evaluation context (ALL, CALCULATE, ecc.)
Time Intelligence functions
http://sqlblog.com/blogs/marco_russo
http://sqlblog.com/blogs/marco_russo/archive/2010/01/19/abc-analysis-in-powerpivot.aspx
http://blogs.msdn.com/powerpivot/archive/2010/04/12/time-intelligence-functions-in-dax.aspx
www.powerpivot.com
www.powerpivot-info.com
http://powerpivotfaq.com/
www.powerpivotpro.com
www.microsoft.com/teched
www.microsoft.com/learning
http://microsoft.com/technet
http://microsoft.com/msdn
BIU302 – Enriching Microsoft PowerPivot for Microsoft Excel Applications Using DAX
Thursday, June 10 | 9:45 AM - 11:00 AM | Rm 244
Thursday, June 10 | 1:30 PM - 2:45 PM | Rm 240