First things first, here you will find the pbix

The universal truths

1. The hidden iterator
   Whenever there is a simple DAX like this SUM(‘<tablename>’[column name]), it’s necessary to remember that this is translated to SUMX(‘<table name>’ , [column name])
2. The filtered table
   Whenever a table is referenced like so ‘<table name>’, or using these table expressions VALUES(‘<tablename>’[columnanme]) or DISTINCT(‘<table name>’[column name]) the current filter context will be applied.
3. The boolean filter
   The simple boolean filter expression in this statement CALCULATE([measure name] , ‘<tablename>’[column name] = <value>) is just syntax sugar (meaning less typing) and will be translated to this CALCULATE([measure name] , FILTER(ALL(‘<table name>’[column name]) , ‘<table name>’[column name] = <value>)).
   With a little imagination, this will allow us to deduct that a filter is a table, as the DAX function FILTER(…) returns a table.

It’s necessary to always remember the following:

“All the DAX filters are tables!”

Jeffrey Wang, “DAX Overview with Jeffrey Wang – Portland Power BI UG”, https://www.youtube.com/watch?v=bJtRB86n9tk

Mr. Miyagi once said: “Wax on, wax off. Wax on, wax off.”

I try to keep these wise words of Mr. Miyagi in my mind, and whenever something does not work as I’m expecting, I review my DAX checking, if I’m aware of these three universal truths.

This report page is a reminder that the Power BI magic is based on DAX. And that there is something called implicit filter and implicit measures. If there are implicit filters and implicit measures, there have to be explicit measures and explicit filters. Whenever we create/compose/build a measure, this measure is an explicit measure. Whenever we use CALCULATE or CALCULATETABLE to alter the existing filter context, we create one or more explicit filters.

This report page shows how implicit filter work, and provide a first glimpse into one of the fundamental concepts, namely filter propagation. This concept will be described in much more detail in blog posts under the category data modeling and filtering.

This report page is a more complex report page without using any explicit measures. Nevertheless, Power BI generates some DAX statements to retrieve data from the data model to provide the “requested” data for each visual. It’s also good to know that each visual is fed by its own DAX query, this means the more visuals, the more queries.

The page contains a page level filter, a slicer, and axis label and row headers, all these are creating implicit filters. We have to be aware that using page level and report level filters can become tricky if we are using the same column in filter modifying DAX functions like ALL.

Whenever I’m using a report level filter I ask myself: Does my data model only contains the data I need.

The page also contains one of the generated DAX queries to feed a visual. This DAX query has been captured using DAX Studio. This statement shows that using the numeric column ‘Fact Sale’[Quantity] in of the visuals (the Matrix visual or the Stacked column chart) leads to the creation of an implicit measure inside the SUMMARIZECOLUMNS(…) function. The captured DAX query also shows that the slicer selection and page level filter are used to create filter tables by using the DAX function TREATAS(…). The filter tables are used inside the SUMMARIZECOLUMNS(…) function.

This report page has two simple table visuals

• We (may) expect a "Total" average of 5 (left table visual)
• We have to change the existing filter context

Both tables are based on the table “the average thing.” This is a very small table that just contains seven rows.

Inside the left table visual, there are already two numeric values shown, Revenue and Average of Revenue. Each time the numeric column ‘this average thing’[Revenue] is used. For the 2nd time, the aggregation function Average is used (https://docs.microsoft.com/en-us/power-bi/service-aggregates, even if this describes how to change the aggregation in Power BI Service, the same is valid for Power BI desktop).

Here we are realizing that the magic of Power BI is not matching our expectation, as we may expect an average of 5 for the total line, and not ~3.57.

To decide what has to be adjusted, our expectation or the total value, it’s necessary to understand what’s happening.

As already mentioned, a row header (the Company column) is an implicit filter. This means that all the rows of the table are filtered by this value and grouped by the aggregation function. Next to the filtering, it’s also necessary to remember that Power BI tries to group all rows by non-numeric columns (Company) and aggregate numeric columns. The following picture shows what is happening for each of the Companies:

It’s imperative to understand that the same is happening for the total line, but as there is no row header present, meaning no filter exists, all the rows will be aggregated. This explains the Total value of 25 and also the Average value of ~3.57 (25 / 7), 7 is the number of rows that are considered, the filtered table. As there is no filter, this means all the rows. This is shown in the next picture:

If we do not want to adjust our expectation, and that the Total Line has to show an average of 5, we have to create a measure that calculates the average of the total line by dividing the sum of the individual averages by the number of companies.

This is exactly what the measure ms A - 1 - average with iterator does.

At this moment, it’s not necessary to understand exactly what this measure does. It’s just used to explain why we need a measure to meet our expectations.

The 2nd table visual (the right one) demonstrates another straightforward use case why a measure becomes necessary. Here we want to calculate the percentage of revenue contribution for each company. To show the .36 (I have been lazy with proper formatting) we have to divide the company revenue by the total revenue. This means we have to remove the filter that is applied by the row header to be able to create a division like this 9 / 25.

This is what measure ms B - 1 -percentage of revenue does.

It divides the individual revenue (the 1st parameter of the DIVIDE function) by the revenue of all companies (the 2nd parameter of the DIVIDE function).

The above business rule to calculate the contribution percentage for each company is simple. We just have to remove the existing implicit filter applied by the row header. For this, we can use the CALCULATE(…) function as it allows to modify an existing filter context inside the current evaluation context.

What?!

Now it’s time to introduce the mysterious evaluation context.

Each DAX statement will be evaluated inside a given context, this context is called evaluation context. The evaluation context groups two other contexts:

• Row context
• Filter context

Being able to reliably differentiate between the row context and the filter context is of paramount importance.

The most obvious context (if a context can be obvious at all) is the filter context. As soon as we have connected to a data source and start exploring the data by dragging columns to the innocent canvas (even before we start thinking about designing a data model), we have to consider the filter context. This is because of how Power BI translates our interactions into DAX. As soon as we start writing measures we must remember that these measures will be evaluated inside a given context – the filter context.

Writing calculated columns is a little different, the evaluation of the DAX statement always starts inside a row context. It’s important to know that a row context can be “transformed” into a filter context by using the wonderous and maybe mysterious function CALCULATE or CALCULATETABLE. A filter context can never be transformed into a row context.

Sometimes it’s not easy to see if the cursor in our DAX statement is inside a row context or filter context. This happens when the DAX statement becomes more complex, or when there is a “hidden” row context that we are currently not aware of.

To identify the type of context just reference a column by using this syntax ‘<tablename>’[columnname] when this is possible meaning no error is raised, then a row context is present. This will reference the column columnname of the current row inside the row context.

If we have to wrap the column reference into one of the aggregation functions like SUM or AVERAGE, then a filter context is present. This means that the values of [columnname] from the rows of ‘<tablename>’ that have “survived” the filtering will be aggregated.

As already mentioned, row and column header, slicer, axis label, and course visual, page, and report level filter create implicit filters. All these filters will be applied to the table used by the expression. To modify the filter context, we can use the DAX function CALCULATE(<expression>, filter 1, …, filter n).

In the percentage example, the table modifier function ALL is used to modify the table. Sometimes it is helpful to imagine that ALL(…) removes an existing filter, but to be more precise, it’s helpful to understand that ALL(…) blocks the specified table, or column(s) from being be a part of the newly created filter context. As ALL(…) blocks the company column from being part of the filter context, the expression SUM(…) will aggregate all the rows, as no other filter is present.

There is also a 2nd version of the percentage measure available that uses a variable to cache the total revenue. This avoids multiple executions of the 2nd parameter.

CALCULATE is the most powerful function as it allows us to alter the existing filter context. The same is valid for the function CALCULATETABLE, whereas this function is used when we want to create a table and it becomes necessary to change the filter.

Whenever we use CALCULATE it’s mandatory to be aware of the fact, that the execution of CALCULATE is performed in 4 steps, these steps are (the epic blog by Jeffrey Wang is a must-read: http://mdxdax.blogspot.com/2011/03/logic-behind-magic-of-dax-cross-table.html):

1. Clone the existing filter context to create a new filter context.
2. Transform an existing row context into a filter context (all the column values of the current row are transformed into a filter context) by blocking the existing filter context in the new filter context.
3. Evaluate each set filter argument in the old filter context, block the existing filter in the new filter context, and add the evaluated set filter argument to the new filter context.
4. Evaluate the 1st argument of the CALCULATE in the new filter context, the cloned and adjusted filter context.

The above steps are performed for each CALCULATE (or CALCULATETABLE), it’s necessary to mention, whenever we are referencing a measure in our DAX statements, the measure is wrapped inside a CALCULATE, meaning the above listed steps are performed.

Now it’s time to explain the measure “ms A - 1 - average with iterator” in greater detail.

Here is the code of the measure

Please be aware that line 5 is just a comment.

What happens is this:

• As AVERAGEX (one of the table iterator functions) is used, this means that the average is computed from all the values that are computed by the 2nd argument CALCULATE(…)
• As the 1st parameter of AVERAGEX is a table, each evaluation of the measure means an iteration across the table, so to avoid unexpected outcomes, it’s mandatory to always understand the size (especially the number of rows) of the iterated table. As VALUES(…) iterates across the filtered one-table column of …[Company] ( see universal truths – the filtered table). As the row header of the used column, creates an implicit filter, there is just one iteration for each Company A, B, C, and three iterations for the  Total line.
• CALCULATE is used around the expression, this means the row context is (the current row of the iteration) is transformed into a filter context. The effect of using CALCULATE or not is only visible in the total line. Not-using CALCULATE means the three iterations will always use the unfiltered table (all the 7 rows) to calculate the average: 25 / 7 eq ~3.57, meaning (3.57 + 3.57 + 3.57) / 3

The measure ms B - 1 -percentage of revenue is somewhat simpler:

What happens is this:

• CALCULATE is used to allow us to alter the filter context, this is done by using ALL(…) one of the table modifier functions, like ALLEXCEPT, or ALLSELECTED. This means that the dividend is calculated by using all the rows with the removed filter from the column …[Company]

The measure used in this example is not complex, but it shows what happens when the measure is used and the same column creates an implicit filter (column …[Sales Territory] is used). As we can see the existing filter that is applied through the row header is overwritten and by the value that is used inside the filter argument, namely Plains. Being more precise, using CALCULATE, leads to this:

1. The existing filter context is cloned
2. An existing row context is transformed into a filter context (here we do not have a row context)
3. The set filter expression (“… = Plains”) is evaluated in the old filter context, the existing set filter is blocked, and the evaluated filter is added to the new filter context
4. The expression is evaluated under the new filter context

The universal truth that we should remember - the boolean filter.

This page shows variations of the simple measure, but this time, the result is calculated only when the row header matches the explicit filter value (the boolean expression inside the CALCULATE(…).

The measure “the simple measure V1” uses KEEPFILTERS(…), KEEPFILTERS prevents the blocking of existing filters in the new filter context. This can be imagined as two instances of the column …[Company] are existing in the new filter context, values of both columns are compared with the filter value.

The measure “the simple measure V2”, may seem a little odd, because of just using a table reference, but this means that the filtered table is used, as a row header creates an implicit filter a lot of comparisons will default to FALSE, as Rocky Mountain does not match Plains, etc.

The measure “the simple measure V3”, is my personal favorite as it is concise (number of typed characters).

This can be considered a recap, in case the pbix is used for some personal learning.

For quite some time, I have been talking about measures, without mentioning calculated columns. From personal experience, I can tell that learning of the existence of something called row context has been challenging. I had some hard times to realize the following three facts:

• Creation of calculated columns starts with the absence of a filter context
• Creation of calculated columns starts with a row context
• Using an aggregation function like SUM(…) inside a row context aggregates the filtered rows, meaning all the rows of the table inside the row context, as this happens after filters have been applied.

The measures cc A – 1 …, cc A – 2 …, and cc A – 3 … show exactly this.

This page uses a very very simple dataset, just four rows, and two columns, but nevertheless, guessing the results for the Total line of both calculated columns can become challenging 😊 For this reason, take your time.

Always keep in mind, that all about calculated columns, has to be used whenever we are creating tables using DAX and are adding “virtual” columns.

Don't hesitate, start learning :-)