In this DAX tutorial, we’ll discuss context and its significance to understanding how DAX calculation engine works in Power BI.
The idea of contexts in Power BI isn’t easy and it’s often overlooked. However, we need to remember that we always need to include the concept of context into our minds, especially when dealing with DAX calculations.
Learning and mastering DAX requires a deep understanding of using context in Power BI.
Understanding Context In DAX Calculations
Measures in Power BI need to have an environment in which they do their calculations. This environment is what we call context.
This can be sourced from a variety of locations. Each result in Power BI is calculated independently based on this context.
As an example, let’s look at these visualizations. We’ll use the result 139,152.30.
For this calculation, the initial context is the date. We’re getting this result based on the context it’s coming from, which is the 27th of September 2019.
Thus, dates in tables are one of the places that a context can come from.
A date slicer can also be used as the source of the context in our reports.
A slicer provides a date range which can further drill into specific dates based on a selection. So, it’s creating an encompassing context on our report page.
Other than dates, we can also use products as our context. To demonstrate, let’s select products in our slicer.
The selections we made are creating context for the results in the table.
We can see that the value for the 27th of September has become 64,661.70 because of the context created from our data and products.
Moreover, we can also create context from the Customer name slicer.
Even if we change the context we’re using in the selections, the Total Sales measure will always calculate the same way.
In Power BI, we can write simple formulas and promptly get a huge number of results through slicer selections because of the context we layer on these codes.
It’s very impressive in terms of speed. We can quickly achieve results with basic formulas and changes to the context.
We’re not only making a selection when using a slicer; we’re also changing the context of the outcome.
Slicers are not the only means of doing this. We can also change the context by making selections within charts.
In the example below, we can look at our Sales by Channel using the donut chart.
This is how we get the result of 40,052.60 for the same date in 2019.
How Context Works In DAX Calculations
A filter is positioned over tables every time we make a selection in our reports.
If we select products, for instance, this will reflect in our Products table. Then, a process called filter propagation happens in the formulas.
Let’s look at this using a model perspective.
Our Products table has a “one to many” relationship to our Fact table, Sales.
The Customers table also has this similar relationship.
And since we have filters for our dates, this means that we also have a relationship between the Dates table and the Sales table.
There’s also another filter coming from the Channel column in our Sales table.
These filters create the context we place in our reports. The selections are streaming down to the Sales table, filtering the results based on the selections made in slicers and charts.
Furthermore, another important technique we can use is having a Customer Name Index in the Sales table.
This can substantially reduce an 8,000-row table to a few hundred depending on the context we apply on the results.
The Total Sales formula can also produce the same outcome; however, it will only work within smaller subsets of the original table.
I hope that you get a lot from this DAX tutorial. This is quite helpful especially when you’re just starting with Power BI. Understanding DAX context how the DAX calculation engine works is an integral part when learning Power BI.
Regardless of the data scenario, DAX calculates and provides results based on the context and filters applied in the report.
What you’ve learned from this DAX tutorial are the basics. There are several ways to use and implement the idea of context in more advanced DAX techniques. These are commonly encountered when we get to virtual tables.
So, always remember this concept for every result that appears in Power BI models.
All the best,