Slide 1

Slide 2

Slide 3

Slide 4

Slide 5

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

Slide 6

Slide 7

Using DAX Calculated Columns

Slide 8

Using DAX Measures (or Calculated Field)

Slide 9

Slide 10

Sales Table [Amount] = [Qty] * [Price] [Unit Cost] = RELATED (Product [Cost]) [Total Cost] = [Qty] * [Unit Cost] [Margin] = [Amount] [Total Cost] MarginPct = [Margin] / [Amount] Customer table [Sales] = SUMX (RELATEDTABLE(Sales), Sales[Amount])

Slide 11

Slide 12

Implicit Measures Automatically defined for all numeric columns Created measure Sum of 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] )

Slide 13

Slide 14

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

Slide 15

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])

Slide 16

Slide 17

Date and TimeInformationMath and TrigStatisticalText DATEISBLANKABSAVERAGECHAR DATEVALUEISERRORCEILING, ISO.CEILINGAVERAGEACODE DAYISLOGICALEXPCOUNTCONCATENATE EDATEISNONTEXTFACTCOUNTAEXACT EOMONTHISNUMBERFLOORCOUNTBLANKFIND HOURISTEXTINTMAXFIXED MINUTELNMAXALEFT MONTHLogicalLOGMINLEN NOWANDLOG10MINALOWER SECONDIFMODMID TIMEIFERRORMROUNDREPLACE TIMEVALUENOTPIREPT TODAYORPOWERRIGHT WEEKDAYFALSEQUOTIENTSEARCH WEEKNUMTRUERANDSUBSTITUTE YEARRANDBETWEENTRIM YEARFRACROUNDUPPER ROUNDDOWNVALUE ROUNDUP SIGN SQRT SUM SUMSQ TRUNC

Slide 18

Table Functions in DAX Differences between Excel and PowerPivot Excel doesnt 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) Differences from Excel

Slide 19

Table Functions in DAX Rows of related table following the relationship RELATEDTABLE (Table) Filter rows from Table according Condition (whenever that Boolean expression evaluates to true) FILTER (Table, Condition) Table made of a single column containing all the distinct (unique) values of a table column DISTINCT (Column) As DISTINCT, but also returns unknown (i.e. invalid relationships) VALUES (Column) Returns all data ignoring filter context and avoiding duplicates ALL (Table), ALL (Column) Returns rows from a table using filter context only for specified columns ALLEXCEPT (Table, Col1, Col2,..) Functions List

Slide 20

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) )

Slide 21

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] = SUMX (Sales, [Qty] * [UnitCost]) [Margin]= [Sales] [Cost] [Margin%]= [Margin] / [Sales]

Slide 22

Slide 23

DAX at work Weight Percent ABC / Pareto Time Period Comparison Customers / Products (distinct count) Many-to-many relationships

Slide 24

Regular CALCULATE syntaxShortened CALCULATE syntax AmountQuotaCustomers = SUM( Orders[Amount] ) / CALCULATE( SUM( Orders[Amount] ), ALL( Customers ) ) AmountQuotaProducts = SUM( Orders[Amount] ) / CALCULATE( SUM( Orders[Amount] ), ALL( Products ) ) OrdersAmount = SUM(Orders[Amount] ) AmountQuotaCustomers = OrdersAmount / OrdersAmount ( ALL( Customers ) ) AmountQuotaProducts = OrdersAmount / OrdersAmount ( ALL( Products ) )

Slide 25

Slide 26

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%

Slide 27

ABC / Pareto SalesAmountProduct

Slide 28

ABC / Pareto CumulatedProduct

Slide 29

ABC / Pareto SortedWeightProduct

Slide 30

ABC / Pareto ABC Product

Slide 31

Slide 32

Time Period Comparison 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 Year-To-Date [Amount YTD] = CALCULATE( SUM( Orders[Amount] ), DATESYTD( Dates[Date] ) ) [Amount YTD 2] = TOTALYTD( SUM( Orders[Amount] ), Dates[Date] )

Slide 33

Time Period Comparison Corresponding value on previous year Use DATEADD to change filter context (SAMEPERIODLASTYEAR as alternative) Total value on previous year Use PARALLELPERIOD to get complete period (year) AmountTotPrevYear = CALCULATE( SUM( Orders[Amount] ), PARALLELPERIOD( Dates[Date], -1, YEAR ) ) Previous Year AmountPrevYear = CALCULATE( SUM( Orders[Amount] ), DATEADD( Dates[Date], -1, YEAR ) ) AmountPrevYear2 = CALCULATE( SUM( Orders[Amount] ), SAMEPERIODLASTYEAR( Dates[Date] ) )

Slide 34

Time Period Comparison 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] ) ) ) Last 12 months

Slide 35

Slide 36

Number of Unique Customers / Products Distinct Count Measure

Slide 37

Slide 38

Slide 39

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

Slide 40

Slide 41

www.microsoft.com/teched www.microsoft.com/learning http://microsoft.com/technet http://microsoft.com/msdn

Slide 42

BIU302 Enriching Microsoft PowerPivot for Microsoft Excel Applications Using DAX Thursday, June 10 | 9:45 AM - 11:00 AM | Rm 244

Slide 43

Slide 44