There is perhaps no more important subject to understanding DAX than context. Context is essential to DAX and is also something that is relatively unique to the language. In fact, context is so fundamental to DAX that a DAX calculation cannot return a result without context. Thus, understanding context is crucial to understanding DAX as it is context that provides much of the unbridled power of the DAX language. Conversely, context also contributes significantly to the learning curve for the DAX language.

Official Microsoft documentation cites three types of context:

• Row context
• Query context
• Filter context

Most other sources essentially ignore the concept of query context, and the Microsoft documentation is somewhat vague regarding this concept. The best analysis is that the combination of row and filter creates the final query context for DAX to retrieve the required data from the underlying data model for the requisite calculation. Users essentially only ever explicitly define row and filter context for DAX, and DAX itself implicitly creates query context from the row and filter context. Thus, we will focus on row and filter context in this recipe.

To prepare for this recipe, perform the following steps:

1. Create a table using the following formula:

R04_Table = GENERATESERIES(DATE(2020,1,1),DATE(2022,12,31))

2. Create a measure in that table using the following formula:

CountOfDays = COUNT([Value])

To demonstrate row context at work, create the following three columns in the R04_Table table:

Year = [value].[Year]
Month = [value].[Month]
Weekday = FORMAT([Value],"dddd")

To demonstrate filter context, perform the following steps:

1. Create a Report page and place a Matrix visualization on the page.
2. Within the Matrix visualization selected, place the following columns and measures from the R04_Table table in the indicated fields for the matrix visualization:

3. Note that the matrix displays the following information:

4. Place a Slicer visualization on the same page and place the Weekday column from the R04_Table table in the Field for the slicer.
5. Select Saturday from the slicer. The Matrix visualization now displays the following:

With regard to row context, DAX automatically applies row context to any calculated column. Therefore, the three columns created, Year, Month, and Weekday, all have row context applied. This is why there is a single value returned despite the fact that we have no aggregation function applied. Thus, within row context, references to columns such as [Value], when not referenced from within an aggregation function, always return a single value, the value of the referenced column in that row. This is really as complex as row context gets, with the exception that it is possible to create row context outside of tables and calculated columns. To create row context within measures, we can use certain DAX functions such as ADDCOLUMN.

Filter context is somewhat trickier. Filter context is created by the combination of visuals and the fields within those visuals, as well as explicit filters created using the Filters pane in Power BI Desktop or directly within a DAX calculation when using a filters clause. In step 3, the matrix rows and columns define the context for the CountOfDays measure. Thus, for each cell, excluding the Total cells, we get the number of days in each month for each year. This is why the cell intersecting February and 2020 has 29, and 2020 is a leap year. The Total column removes the filter context for the individual columns but not the individual rows, and so we get the total number of days for all three years, 2020, 2021, and 2022, for each month. Conversely, the Total row removes the filter context for the individual rows but not for the individual columns, and so we get the total number of days in each year. Finally, the cell on the right in the bottom row removes the filter context for both the individual rows and individual columns, and so we get the total number of day in all three years. Therefore, the filter context for this cell is effectively no filters or all data referenced by the matrix visualization.

Adding the slicer and selecting an individual weekday adds additional filter context to the matrix since the default in Power BI Desktop is to cross-filter visualizations. Thus, in addition to the filter context of the individual rows and columns in the matrix, the cells also encapsulate the filter context of the slicer, and so we are presented with the number of Saturdays in each month of each year with their corresponding totals in the Totals row and column. Selecting a different weekday from the slicer, or a combination of weekdays, will present their corresponding counts in the matrix visualization.

Create a new column in the R04_Table table with the following formula:

Days = COUNT([Value])

For more details regarding this recipe, refer to the following links:

• DAX overview – context: https://docs.microsoft.com/en-us/dax/dax-overview#context
• The most important DAX concept: https://powerdax.com/important-dax-concept/
• Understanding evaluation contexts in DAX: https://www.microsoftpressstore.com/articles/article.aspx?p=2449191