This tutorial will discuss about Power BI filters. You’ll learn how to see context transition in the xmSQL and the logical query plan. The context transition transforms the current row context into an equivalent filter context. The filters are then triggered by the CALCULATE function.
The Importance Of The CALCULATE Function
The CALCULATE function creates the context of your DAX calculations. If you’re using an iterating function without wrapping it in CALCULATE, you’ll get inaccurate results.
Here is a simple query with a no-context transition:
It has the SUMX function for the product of the FactSales’ Current Price and Quantity. It then adds a column to every year in the Calendar Year table for Total Sales.
If you run the query, you’ll see that the results of each row are the same.
Repeating results mean something is not right because it’s virtually impossible to have the same amount of sales every year. Your DAX measure or data model may need revisions or adjustments. In this case, the DAX measure doesn’t have CALCULATE wrapped in it.
If you click the first scan and look at the xmSQL statement, you can’t see any filter or context indicating that the Calendar Year should be part of the calculation. You need the CALCULATE function to invoke the context transition.
Invoking Filters In Power BI DAX Query
This is the same query but with a context transition using the CALCULATE function.
If you run the query, you can see different values on the results.
The second scan of the DimCalendar Year is to collect all the other years. The results won’t have the rows for years with blank results (2012, 2013, and 2014) without the scan. The storage engine does it in the background to ensure that all data is accounted for.
If you look at the xmSQL statement, you can see that the Left Outer Join is invoked by the CALCULATE function. The Left Outer Join gives the sales data of the years 2015 to 2018. If you see the Left Outer Join combined with $Expr0 in the xmSQL statement, it means that there’s a context transition.
Things can get a little tricky when you have multiple context transitions happening. But Left Outer Join informs DAX that you need to focus on the range of data in your filter.
You can actually see this in the logical query plan in the form of Sum_Vertipaq, which depends on the DimCalendar Year.
If you try to run the query with no context transition and look at the logical query plan, you can see that the Sum_Vertipaq doesn’t depend on anything. This indicates that you need to fill something in your data model or measure to get the output you want.
***** Related Links *****
Introduction to Filter Context In Power BI
DAX Tutorial: Context & The Calculation Engine
How To Use Simple Filters In Power BI
Conclusion
This is how you identify a context transition and determine when it’s happening or not. Invoking context transitions is essential in generating valuable results. They help gather data from different ranges, time periods, and tables.
CALCULATE is one of the most important functions when you invoke your filters. Once the calculation is set, the storage engine works on the execution of the query to provide results.
Nick
