1. Context in DAX formulas
Welcome back! In this chapter, we'll take a closer look at context in Power BI. We'll also dive deeper into DAX formulas and understand the use of variables and the CALCULATE function.
2. Introduction to Context
Context is an important concept to understand in DAX. It enables dynamic analysis, where the results of a formula change to reflect the selected data.
There are three types of context - row, filter and query. In this chapter we will cover row and filter context which are key to becoming a DAX master, query context falls outside the scope of this course. You can learn more about query context in future DAX courses.
3. Introduction to Row Context
Row context can be thought of as "the current row".
4. Introduction to Row Context
A calculated column is an example of where row context applies. In a calculated column the context includes the values from all columns within the current row.
5. Introduction to Row Context
In the table below we have a calculated column price_with_tax which contains a formula that does a calculation on the price and tax column. As you can see, the calculation is happening row by row - a perfect example of row context in action.
6. Introduction to Row Context
Row context can also be used in measures, however only when using iterator functions. An iterator function loops through every row in a table and runs the formula for each row.
An iterator function can be identified by an X at the end of a function name. For example SUMX takes two arguments, the table that needs to be iterated over and the calculation that needs to happen, similar to what we'd use in a calculated column,
7. Introduction to Row Context
For example, if we wanted to get the output of our Price_with_tax column without ever creating a calculated column we could use SUMX to iterate over each row and perform the calculation.
8. Introduction to Row Context
This would give us a total value of 70 dollars.
9. Introduction to Filter Context
Filter context is a set of filters that have been applied before the calculation is carried out.
They can be applied in several ways, including attributes in a row or column, via a slider, through the filter pane, or in a calculated measure.
10. Introduction to Filter Context
In this example, we have a table of data and we'd like to apply a filter of blue.
11. Introduction to Filter Context
In order for Power BI to return a value for color blue, it will go to the base table and apply a filter where color is equal to blue. This is the filter context. It is applying the filter context on each quantity returned by color.
12. Introduction to Filter Context
Starting from our original table. We can extend this further by creating a matrix of color versus product category.
13. Introduction to Filter Context
This changes the filter context again, because for each data point that is returned Power BI is applying filter context.
14. Introduction to Filter Context
For Blue Socks - a filter is being applied for color being equal to Blue and product category being equal to Socks.
15. Calculate Function
Now that you've understood Filter context, let's introduce you to the calculate function. Calculate allows you to evaluate an expression with one or more filter contexts.
The calculate functions takes two arguments - one argument is required and the other argument is optional. The first argument contains the expression to be evaluated - this must return a single value.
The second argument for adding filters is optional - but you need to ensure that filters evaluate as a table, and do not clash with one another. For example, Sales City is equal to London and Sales Country is not equal to United Kingdom.
The filters inside the calculate function will always override any filters from a visualization.
You can see an example of calculate in action. Here we are calculating total sales in EMEA region.
16. Let's practice!
Now that you've seen the difference between row and filter context, let's practice what you've learned.