# DAX Calculation – Filter Context Transitions

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.

## 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

Nick Mone, Enterprise DNA Expert
Nick Mone is skilled in creating complex data models and using Power Query to take data in any form and transform it into a tabular format. His interest lies in taking seemingly unyielding complex scenarios and breaking them down into smaller parts.

## Formatting DAX Code In Power BI

Effort here will pay dividends later Placing effort into formatting your DAX code will make a huge...

## Cumulative Totals Based On Monthly Average Results In Power BI

Today, I wanted to cover a unique technique around cumulative totals based on monthly average results...

## Power Automate Expressions – An Introduction

In this tutorial, we’ll explore Power Automate expressions and how to use them in workflow automation....

## List.Max Power Query: User Guide With Examples

One of the most powerful and useful functions in Power Query M language is List.Max. It can be...

## R Scripting For Power BI Using RStudio

In this blog, we’re going to go through the basics of R scripting for Power BI using RStudio. The...

## Simple Aggregations In Power BI

In this blog post, I'll touch on some simple aggregations in Power BI that you can use in your DAX...

## Microsoft Report Builder: Steps In Creating A List

In this tutorial, you will learn how to create a list and page break in Microsoft Report Builder. A...

## 5 Strategies To Enhance Your Power BI DAX Skills

In this tutorial, you’ll learn the general strategies to improve your DAX or M capabilities. This is an...

## ALL Function in Power BI – How To Use It With DAX

Did you know that the ALL function can be used to modify the context of a particular calculation in...

## Calculations In Power BI Using Measure Branching

Measure Branching is a technique in making calculations in Power BI. It's not something you'll hear...

## Power BI Ranking In Hierarchical Form

Today, we will learn how to calculate Power BI ranking in a hierarchical form which is a little bit...

## Understanding Power BI Aggregations

Now, let's delve deeper into one of the most important concepts in Power BI calculations — the...