This post will discuss how to identify a filter via xmSQL and the storage engine query in Power BI. You’ll also learn what VertiPaq is doing when you filter on range. This is an important topic because the cardinality of what’s being applied in the filters is the biggest driving force of DAX’s performance.
xmSQL query is a quasi-sequel language that has the SELECT, FROM, and WHERE clauses. The WHERE clause is your filter, which is introduced when you use the FILTER or CALCULATETABLE function. Filters exist in both the logical query plan and the storage engine query plan.
Filter Context Transition
This sample query has the ROW function with the Total Sales and the SUMX function over the Fact Sales. The Fact Sales is multiplying the Current Price and the Sales Quantity. For this example, you only need to focus on sales that are greater than 300.
Running the query will give you a one-row table result that isn’t very helpful.
However, if you go to the Server Timings tab and scan, you can see the xmSQL statement that gives you information on what your query is doing.
The WITH clause with $Expr0 means that a mathematical expression is being computed. Next, the expression is selected from the sales data table or the fact sales table and then filtered in the WHERE clause with the set conditions.
In the physical query plan, you can see the Filter_Vertipaq operator. It means that the query is being pushed down to the storage engine. As much as possible, use the storage engine as a filter in order to have a quicker DAX performance.
This is another sample query that uses SUMX instead of the CALCULATETABLE function. If you run the query and look at the xmSQL statement, you can see the same result, scan, and query plans.
You can use either the SUMX or CALCULATETABLE function to generate results. But SUMX can sometimes be slower than CALCULATETABLE. It’s best to use the latter to achieve a smoother DAX performance.
Filter On Range In Power BI
This is a sample query that filters on a range. The filter condition gets the sales data if the current price is greater than or equal to 300 but less than 1000.
If you run the query and look at the xmSQL statement, you can see a range in the WHERE clause.
If you look at the physical query plan, you can see 2 Filter_Vertipaq. This is because your filter has a range between 300 and 1000. This also means that the storage engine is working on the query and calculation.
Here is another query with multiple values using the OR clause, which is denoted by “||”.
After you run the query, you can see changes in the WHERE clause inside the xmSQL statement. xmSQL converted the OR statement into an IN statement.
If you use IN in the query, it will yield the same xmSQL statement and results. Remember that when you use IN, it has to be a table which is denoted by curly brackets.
But using an OR or IN clause doesn’t matter because when the storage engine reads it, it knows it has the same xmSQL statement. That’s why it’s giving the same result of $939,012.
***** Related Links *****
Introduction to Filter Context In Power BI
DAX Tutorial: Context & The Calculation Engine
How To Use Simple Filters In Power BI
Conclusion
In this blog post, you learned how to set up a filter condition in your DAX in Power BI. Filters define what results can be generated from your DAX query in Power BI. They are important entities that must be established in order to invoke context transitions. If you want to get specific results from different time periods or ranges, filter context must be introduced in the query.
Nick
