Learn DAX by Studying Common User Problems Jeffrey
Learn DAX by Studying Common User Problems 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.
![Different ways to filter a measure • Calculate(Sum('Sales'[Sales Amount]), Geography[Country] = "United States") •](http://slidetodoc.com/presentation_image_h2/cd73420042a039cb49998c7ef14309d0/image-3.jpg "Different ways to filter a measure • Calculate(Sum('Sales'[Sales Amount]), Geography[Country] = \"United States\") •")
Different ways to filter a measure • Calculate(Sum('Sales'[Sales Amount]), Geography[Country] = "United States") • Calculate(Sum('Sales'[Sales Amount]), Filter(Geography, [Country] = "United States")) • Calculate(Sum('Sales'[Sales Amount]), Filter(All(Geography[Country]), [Country] = "United States"))
Watch out for measures inside an iteration • 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)
Dense measure and large crossjoin • Example 1: Convert BLANK to 0 • If(Is. Blank([Total Sales], 0, [Total Sales]) • [Total Sales] + 0 • Example 2: Yo. Y growth • Yo. Y % Change = [CY Sales] / [LY Sales] – 1
![Replace multiple instances of the same measure by a variable • [Yo. Y growth]](http://slidetodoc.com/presentation_image_h2/cd73420042a039cb49998c7ef14309d0/image-6.jpg "Replace multiple instances of the same measure by a variable • [Yo. Y growth]")
Replace multiple instances of the same measure by a variable • [Yo. Y growth] = Divide([CY Sales] – [LY Sales], [LY Sales])
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())
Visual calcs • Example 1: Grand total of measure • Example 2: Running total
- Slides: 8
