In my last blog post which can be found here, I demonstrated that it’s important to limit your DAX filters to specific columns rather than entire tables, now we know that we can and should do it, let’s explore why and how it works.
There are numerous ways to write an expression that will give the same result set, but why are some ways more efficient than others? To answer that we need to know a little about what happens under the hood, inside the DAX engine(s).
A lot goes into processing a DAX expression but the focus of this blog post will be the Formula Engine (FE) and the Storage Engine (SE).
Both engines do different jobs, think of the FE as the brain, it provides the instructions, and the SE as the muscles, it does the fetching and carrying.
The FE takes a DAX expression and turns it into a set of sequential instructions, (joins, aggregations, filters, etc). It then sends these instructions to the SE. Due to the single-thread nature of the FE to SE communication, the instructions are sent to the SE sequentially. Therefore, the more instructions sent, the longer the entire process takes.
The SE takes these instructions and executes them against the data source(s), collects what is requested and delivers it back to the FE. Unlike the FE, the SE uses multi-threading so can process the FE’s requests very quickly.
So now that we know a little about the FE and SE and what roles they perform, how can we use it to our advantage?
The SE can do a lot of complex work very quickly, but if we can limit the amount of instructions it has to carry out, the quicker the overall process becomes. This is best shown with an example.
*The queries below are being run in DAX studio so the stats can be recorded alongside the result set
Here is a simple expression to select the total amount of sales, per region, taken from everyone’s favourite imaginary online bike store:
DEFINE MEASURE Sales[Count] =
CALCULATE(
DISTINCTCOUNT('Sales Order'[Sales Order]
)
)
EVALUATE
CALCULATETABLE(SUMMARIZECOLUMNS(Customer[Country-Region], "Sales by Region", Sales[Count]))
As you can see, the FE takes the DAX expression and turns it into 1 single query. The instruction is sent, the SE does the hard work and passes the result back. The entire query took 44ms to return 7 rows, one per region. Nice and fast.
However, if we add a filter to the query and use the entire Sales table (highlighted in red below) in the filter, look what happens:
DEFINE MEASURE Sales[Count] =
CALCULATE(
DISTINCTCOUNT('Sales Order'[Sales Order]),
FILTER(
Sales, Sales[Sales Amount] > 350
&& Sales[OrderDateKey] > 20200101
)
)
EVALUATE
CALCULATETABLE(SUMMARIZECOLUMNS(Customer[Country-Region], "Sales by Region", Sales[Count]))
The expression now takes 8 times longer to complete. The reason for this is the FE sends the SE 8 queries, and remember, it must do these sequentially.
1x The SE fetches the table for the filter (Sales) and trims it down to only the rows that meet the filter criteria:
a. Sales Amount > 250
b. OrderDateKey > 20200101
7x That filtered table, which is now held in memory, is applied across 7 more queries, one query per region in the Customer table.
There are 8 times as many instructions sent to the SE and the whole process takes around 8 times as long to complete.
With a little knowhow we can rewrite the expression in a more efficient way, using only the specific columns in the filter (again, in red). This enables the FE to parse it into a single instruction and ping it off to the SE.
DEFINE MEASURE Sales[Count] =
CALCULATE(
DISTINCTCOUNT('Sales Order'[Sales Order]),
FILTER(
ALL(Sales[Sales Amount]), Sales[Sales Amount] > 350),
FILTER(
ALL(Sales[OrderDateKey]), Sales[OrderDateKey] > 20200101)
)
EVALUATE
CALCULATETABLE(SUMMARIZECOLUMNS(Customer[Country-Region], "Sales by Region", Sales[Count]))
The SE receives 1 instruction, produces the same result set and the entire process takes a fraction of the time.
Although the technology behind DAX is very quick, learning the most efficient ways to write expressions can give that technology a helping hand and make your expressions, and therefore your Power BI reports, just that bit quicker.
If you need any help or advice with your DAX expressions or Power BI reports in general, feel free to leave a comment or drop me or any of the team an email with your queries, we would love to hear from you.
Great post, can I just check is the datekey because that’s the field or is it faster to process a number date field rather a field of data type of date?
Thanks once again for the post
Hi Ant,
Thanks for the feedback, its very much appreciated.
To answer your question, I used Datekey because it is what was available to me in the data set I was using. Marco Russo and Alberto Ferrari over at SQLBI recently wrote an interesting article on Date vs Date Key which can be found here: https://www.sqlbi.com/articles/choosing-between-date-or-integer-to-represent-dates-in-power-bi-and-tabular/
The basic conclusion is:
– Basic performance is the same
– Size is the same
– Functionality is much better when using a Date
– DAX Date calculations/logic are faster when using a Date
Hope this helps.
Great post.
For those wanting to follow along at home – you can download the Adventure Works DW pbix sample data model. google it if you dont already have it.
Again, really insightful post thanks Joe.
I have a number of DAX measures filtering all over the place, often, combining 2 different filter criteria from the same table. Typically, I combine them using the && operator. Although this does work…evidence suggests it’s slower than having 2 separate filter statements. Good to know 🙂
e.g.
EVALUATE
{
CALCULATE(
DISTINCTCOUNT( ‘Sales Order'[Sales Order] )
,
FILTER(
ALL(Sales[Sales Amount], Sales[OrderDateKey])
, Sales[Sales Amount] > 350
&& Sales[OrderDateKey] > 20200101
)
)
}