This post is a bit technical and if you don’t know DAX very well you will have trouble understanding it. Nevertheless, read along to get yourself familiar with how DAX works. I have decided to write this because I sometimes forget about this very important concept that every DAX measure is evaluated in an external filter context. Hope that after writing this down, I will never ever forget about this.
I have recently helped one of my clients to optimize some DAX code. I thought it’s an interesting journey and you may learn something from this as well. Before I dive into the problem, let me briefly tell you what DAX is and how important it is if you use Excel Power Pivot or Power BI desktop or SSAS Tabular. Data Analysis Expressions (DAX) is a library of functions and operators that can be combined to build formulas and expressions in Microsoft SQL Server Analysis Services (SSAS) Tabular Model, Power Pivot in Excel, and Power BI Desktop. If you are a business person as opposed to IT and you know DAX very well and also have good understanding of dimensional modeling, you are one foot in the door of potentially becoming a MS SQL BI developer. If you are planning to learn DAX, I highly recommend this book. Excel Power Pivot, Power BI desktop and SSAS Tabular all have the same engine (VertiPaq engine) under the hood, so if you know how to use one, you won’t have too much trouble using the other two.
The problem my client had is he wants to check a measure, say sales $ for the last 12 quarters (inclusive of the current one) and see if they all have sales $>0. If this is true then do X otherwise returns a blank value. For example, for Q3 2016, he needs to check if Q3 2016 and 11 quarters prior all have sales $>0. The way he wrote this is
IF [Sales $ for current quarter] >0 and [Sales $ for prior quarter]>0 and [Sales $ for prior 2 quarters] and [Sales $ for prior 3 quarters]>0……[Sales $ for prior 11 quarters]>0 THEN do X ELSE do nothing. All these measures are calculated using time intelligence function DATESINPERIOD. This takes roughly 30 seconds to return result if you filter your date table to 1 quarter. If you don’t filter your dates table prior it will take very long time to return result. The reason why this is slow because it needs to calculate 12 values for each quarter until then it can decide what to do next. The way I solve the same problem is for each quarter, I dynamically build a virtual table like below, then I filter this table to return rows where Sales $ >0, finally count number of rows of this filtered table to see if row count = 12. Because the existence of relationship between Dates table and Sales table, building such virtual table is extremely fast and filtering a table with only 12 rows is also extremely fast, so it will perform much better than the original approach.
The first measure I wrote was this.
When I tested it, it returned value of 1 for all rows. I overlooked the fact that this was evaluated in an external filter context. Meaure1 removes any external filter on Dates[QuarterID] column by way of ALL(Dates[QuarterID]), but Dates[Year] and Dates[Quarter] columns still in the view and Meaure1 will need to respect this filter. So for row 1 the virtual table I was trying to build only has one row which is 2014 Q4, hence it returned value of 1.
If I remove Dates[Quarter] column and add Dates[QuarterID] column, the number it returned changed. For row with Dates[QuarterID]=201703, the virtual table has 12 rows (201703,201702,201701,201604,201603,201602,201601,201504,201503,201502,201501,201404). because Dates[Year]=2017 is also in the view so the virtual table actually only has 3 rows (201703,201702,201701),hence value of 3 is returned.
If I remove Dates[Year], it returned expected result. However for Meaure1 to work you can only have Dates[QuarterID] in the view for it to return correct result.
A better way to rewrite this formula is as shown below.
And you can see it returned correct result with or without Dates[QuarterID] column.
DAX is a powerful language and you can do magic when you truly master it. It won’t be an easy journey and I can assure you that once you get hang of it, lots of impossibles will be possible just like what this guy has done it the world of soccer.