In this tutorial, you’ll learn about how the storage engine can use the data model to compute information for your DAX calculation. It has the ability to use Left Outer Joins via the data model.
The storage engine can do simple joins by having a one-to-many relationship in your data model or by using the TREATAS function which transfers filters back and forth.
A one-to-many relationship means having a dimension table related to your fact table. DAX knows when you are querying a column with this type of relationship.
Filter In DAX Calculation
Here is a simple data model with four dimension tables and one fact table in a star schema setup. You can also see four keys in the fact table that is related to the primary keys in the dimension tables.
This is the DAX query for the data model above:
You can see that the query adds columns to the values of the DimCalendar year. The query then calls for the Total Sales-Context Transition. When you use the ADDCOLUMNS function, you have to use CALCULATE in order to invoke the context transition to avoid having the same value for the total amount for each row.
After you run the query, you’ll see the xmSQL statement and the results.
In the statement, you can see how the query works. The WITH clause executes the mathematical expression in DAX and then selects the DimCalendar Year. Make sure to pull in the filters from the dimensions table to prevent the performance from slowing down.
Next, the storage engine sums up the expression inside the WITH clause and selects data from the fact sales table by doing a Left Outer Join on the DimCalendar to the Sales Data Purchase Date, which is equal to the DimCalendar Date.
Having the Left Outer Join in the storage engine makes the whole calculation faster. The more work done inside the storage engine, the better the results. Having complex joins slow down DAX’s performance because the formula engine takes over the calculation.
VertiPaq is built for simpler things. It’s simple compared to what the formula engine can do. It’s a matter of how fast it can generate and provide a result.
***** Related Links *****
Introduction to Filter Context In Power BI
DAX Tutorial: Context & The Calculation Engine
How To Use Simple Filters In Power BI
Conclusion
The xmSQL statement shows what your DAX query is doing and how it is performing the whole calculation. In the statement, you can see the flow of work such as the execution of mathematical expressions and context transitions. VertiPaq can invoke these transitions by using the data model with simple functions.
Know more about VertiPaq and xmSQL statement by utilizing this tutorial and exploring the earlier blog posts that talk about the storage engine.
Nick
