Master DAX Measures Jeffrey Wang Engineering Manager Microsoft

Master DAX Measures Jeffrey Wang Engineering Manager Microsoft Power BI jewang@microsoft. com

About me • Started my BI career in 2002 soon after the dot-com bubble burst. • Joined Microsoft in 2004 and stayed in BI engine ever since. • Currently on the Desktop team in charge of the DAX engine. • On the committee which invented DAX.

How do users use DAX in Power BI? • Measures • Calculated columns • Calculated tables • Row level security

Agenda • Learn how DAX measures work • Deep dive into filter context • Make my measures return correct results • Make my measures run fast

Filter Context

Row Context and Filter Context • Row Context • Calculated columns • RLS • Iteration functions • Filter Context • Power BI visuals • Excel pivot tables • Calculate functions

Filter contexts * Filter columns must be columns in the model

A table scan semijoins with filter tables • A table scan semijoins with filter tables with common columns • In DAX, a table reference 'Table' is actually the result of virtual join (left outer join) operations starting from the root table following all many-to-one relationships • DAX expression 'Sales' is a virtual join of 'Sales’ and all its related tables 'Sales' LOJ 'Product Subcategory' LOJ 'Product Category' LOJ 'Date' LOJ 'Customer' …

Bidirectional crossfiltering injects implicit filter expression • For a slicer on a column from the 'Product' table to filter the 'Customer' table when bidirectional crossfiltering is enabled, DAX Engine injects a filter right before the 'Customer' table is scanned that’s equivalent to Calculate. Table('Customer', Summarize('Sales', 'Customer’[Customer. Key]))

Filter contexts apply to leaf nodes • First filter, then calculate • All related filters are applied • Multiple filters on the same column are effectively intersected • In contrast, Filter function performs post filtering semantically

How Does Filter Context Work? Calculate(Sum. X(Filter(Fact. Sales, [Sales. Quantity] > 1000), [Sales. Amount]), Date[Year] = 2011)

Order of operations • [Profit Margin] = (Sum(Sales[Amount]) – Sum(Sales[Cost])) / Sum(Sales[Amount]) • Calculate([Profit Margin], 'Product'[Name] = "P 1")

DAX functions that work with filter context

DAX was inspired by existing programming languages • Excel formulas – math and trigonometry, statistical, date/time, text, logical, information, financial • SQL – relational algebra operations • MDX – measures, implicit join

DAX functions from Excel • +, -, *, /, ^, =, >, <, >=, <>, &, Date. Value, Day, EDate, EOMonth, Hour, Minute, Month, Now, Second, Time. Value, Today, Week. Num, Year. Frac, Date, Is. Blank, Is. Error, Is. Logical, Is. Non. Text, Is. Number, Is. Text, Is. Even, Is. Odd, And, False, If. Error, Not, Or, Switch, True, Abs, Ceiling, ISO. Ceiling, Exp, Fact, Floor, Int, Ln, Log 10, Mod, MRound, Pi, Power, Quotient, Rand. Between, Round. Down, Round. Up, Sign, Sqrt, Trunc, Acosh, Acoth, Asinh, Atanh, Combin, Cosh, Coth, Degrees, Even, Odd, Permut, Radians, Sinh, Sqrt. Pi, Tanh, Chisq. Dist. Rt, Chisq. Inv. Rt, Combina, Confidence. Norm, Confidence. T, Expon. Dist, GCD, LCM, Norm. Dist, Norm. Inv, Norm. S. Dist, Norm. S. Inv, Poisson. Dist, Beta. Inv, T. Dist. 2 T, T. Dist. Rt, T. Inv. 2 T, T. Inv, Blank, Concatenate, Exact, Find, Fixed, Left, Len, Lower, Mid, Replace, Rept, Right, Search, Substitute, Format, Trim, Upper, Value, Unicode, Unichar, XNPV, XIRR

DAX functions inspired by SQL • • • Selection: Filter, IN, Contains. Row Top. N: Top. N, Sample, Top. NSkip Projection/Rename: Select. Columns, Add. Columns Group. By – Distinct, Values, Summarize, Group. By Aggregation functions: Is. Empty, Concatenate. X, Sum. X, Average. A, Average. X, Count. Rows, Count. AX, Count. Blank, Distinct. Count. No. Blank, Max. X, Max. A, Median. X, Min. A, Percentile. Exc, Percentile. Inc, Percentile. X. Exc, Percentile. X. Inc, Rank. EQ, Rank. X, Stdev. S, Stdev. X. P, Stdev. P, Var. S, Var. X. P, Var. P, Product. X, Geomean. X, Current. Group Joins: Cross. Join, Generate. All, Natural. Left. Outer. Join, Natural. Inner. Join, Full. Outer. Join Set functions: Union, Intersect, Except Table construction: {}, Row, Data. Table, Generate. Series Fetch column value: Related, Lookup. Value Hierarchical functions: Path, Path. Item. Reverse, Path. Length, Path. Contains

DAX functions inspired by MDX • Filter context functions: Is. Cross. Filtered, Has. One. Value, Is. Filtered, Has. One. Filter, Remove. Filters, All. Except, All. No. Blank. Row, All. Selected, Calculate. Table, Filters, Values, Selected. Value, Treat. As, Use. Relationship, Cross. Filter, Keep. Filters, Is. In. Scope, Non. Visual • BI query functions: Summarize. Columns, Rollup. Group, Rollup. Add. Is. Subtotal, Rollup. Is. Subtotal, Ignore, Add. Missing. Items • Time intelligence functions: Date. Add, First. Date, Last. Date, First. Non. Blank, Last. Non. Blank, Start. Of. Month, Start. Of. Quarter, Start. Of. Year, End. Of. Month, End. Of. Quarter, End. Of. Year, Dates. Between, Dates. In. Period, Parallel. Period, Previous. Day, Previous. Month, Previous. Quater, Previous. Year, Next. Day, Next. Month, Next. Quarter, Next. Year, Dates. MTD, Dates. QTD, Dates. YTD, Same. Period. Last. Year, Total. MTD, Total. QTD, Total. YTD, Opening. Balance. Month, Opening. Balance. Quarter, Opening. Balance. Year, Closing. Balance. Month, Closing. Balance. Quarter, Closing. Balance. Year

Convert Row Context to Filter Context • Calculate/Calculate. Table • • Convert all rows in row context into filters in filter context Add new filters Remove filters Replace existing filters • [measure] : = <expression> is actually Calculate(<expression>) • All rows have already been converted to filters before <expression> is evaluated • 'Table'[Column] does not work at the top level of a measure expression • Filter is an iteration function. It doesn’t change filter contexts.

Common DAX patterns that may produce unexpected results

Set simple filters • The following two ways of setting filters are different: 1. 2. Calculate(Sum('Sales'[Sales Amount]), Geography[Country] = "United States") Calculate(Sum('Sales'[Sales Amount]), Filter(Geography, [Country] = "United States")) • Option 1 is equivalent to 1. 1. Calculate(Sum('Sales'[Sales Amount]), Filter(All(Geography[Country]), [Country] = "United States")) • Expressions affected by surrounding filters: • • • Count. Rows(Geography) Count. Rows(Distinct(Geography[Country])) Count. Rows(Values(Geography[Country])) Has. One. Value(Geography[Country]) Is. Filtered(Geography[Country]) Sum('Sales'[Sales Amount]) is equivalent to Sum. X('Sales', [Sales Amount]) • Expressions not affected by surrounding filters: • Filter(All(Geography), [Country] = "United States") • All(Geography[Country])

Use DAX variables to avoid unexpected side effects • Using measures on the right-side of iterator functions (Filter, Sum. X, etc. ) may pick up unexpected filters Current Date = Max('Date'[Date]) YTD Sales = Calculate( ) [Total Sales], Filter(All('Date'[Date]), Year([Date]) = Year([Current Date]) && [Date] <= [Current Date]) • Solution: Use variables to store measure values in the correct context YTD Sales = Var v. Current. Date = [Current Date] Return Calculate( ) [Total Sales], Filter(All('Date'[Date]), Year([Date]) = Year(v. Current. Date) && [Date] <= v. Current. Date)

Time Intelligence Functions

Excel date/time functions vs. DAX time intelligence functions • DAX stores dates as sequential numbers representing the number of days starting from 12/30/1899. • Example: convert number of seconds into Date. Time: • Date(1899, 12, 30) + <number of seconds> / (24 * 60) • Time(0, 0, <number of seconds>) has limitations • Arithmetic operators and Excel functions operate on a single date/time value. • • • Extract components from a date/time value Construct a date/time value from parts Convert a text value to a date/time value Shift a single date Often used to add calculated columns Can be done in M as well • Don't use DAX in M editor • DAX time intelligence functions operate on a filtered date column. • Shift selected continuous dates • Primarily used in measures

Excel date/time functions vs. DAX time intelligence functions (Examples) Scenario Right way Wrong way 7 days ago from today Today() - 7 Date. Add(Today(), -7, DAY) 1 month ago from today EDate(Today(), -1) Date. Add(Today(), -1, MONTH) the last day of this month EOMonth(Today(), 0) End. Of. Month(Today()) the first day of this month Today() – Day(Today()) + 1 Start. Of. Month(Today()) Scenario Use time intelligence functions Flexible Use Excel operators/functions Inflexible year over year sales Same. Period. Last. Year, flexible Filter(…, [Calendar Year] = Values([Calendar Year]) – 1) year to date sales Total. YTD, flexible Filter(…, [Date] <= Values([Date]) && [Date] >= Date(Year(Values([Date])), 1, 1))

DAX time intelligence function example: Same. Period. Last. Year

DAX time intelligence functions shift continuous dates • Common error message Mdx. Script(Model) (X, XX) Calculation error in measure 'XXX'[X]: Function 'DATEADD' only works with contiguous date selections. • Examples 1. Multi-selection of years 2. Bi-directional cross-filtering to the Date table • Time intelligence functions work on filtered date column.

Hidden date tables and DAX dot notation • Power. BI automatically creates a hidden data table for each date/time column • A uni-direction relationship is created between the visible date/time column and the hidden date table • Users can access the columns in a hidden date table using DAX dot notation through the visible date/time column • [Order Date]. [Date] is equivalent to Related('Hidden Table'[Date]) • [Order Date]. [Year] is equivalent to Related('Hidden Table'[Year])

Common DAX patterns that produce slow queries

Distinct. Count with changing filters • Calculate(Distinct. Count('Sales'[Customer. Key]), Except(All('Product'), 'Product’)) • Calculate(Distinct. Count('Sales'[Customer. Key]), Filter(Values('Product'[Standard Cost]), [Standard Cost] > 100))

Avoid dense measures combined with columns from different dimension tables • BLANK value is your friend in BI queries. • Examples of dense measures: • [measure] = 1 • [measure] = Count('Sales'[Product. Key]) + 0 • [measure] = If ([Total Sales] <> 0, [Gross Margin] / [Total Sales], 0) • The combination of many columns from different dimension tables and a dense measure is a leading cause of out-of-memory errors.

Replace multiple instances of the same measure by a variable • [Yo. Y growth] = Divide([CY Sales] – [LY Sales], [LY Sales])