Power BI October 2024 Release Notes – Part 2

Part 2:

As discussed last week, I go through the monthly release notes for Power BI and pull out the best and most useful. Last week we looked at Snowflake and new Slicers. This week, it is all about Value Filters. This is taken from the Power BI Blog post.

Value filter behavior (preview)

DAX has an automatic filtering mechanism that occurs when multiple columns from the same table are filtered. This behavior is informally called ‘auto-exist’. DAX understands that not all combinations of values across these columns are valid and as a result it automatically excludes invalid combinations. The DAX Engine generated a coalesced value filter that not only returns valid combinations but also affects measured calculations. This month we are giving you more control over whether you want this behavior in your semantic model. You can decide whether you want to turn off coalesced values filters and turn on independent value filters instead. Turning on independent value filters by setting the ‘Value filter behavior’ setting to Independent (see below) results in multiple filters on the same table being kept separate instead of the DAX engine combining these into one.

Understanding current value filter behavior

When you are filtering multiple columns on the same table, the current default value filter behavior takes these filters and combines them into one, considering only the combinations that exist. Consider the following two columns on the same table:

• Year, which contains values like ‘2023’.
• Month, which contains values like ‘January 2024’.

If you filter on both Year and Month, since these columns are on the same table, the value filter behavior combines the filters into one, but only the combinations that exist are considered. Of course, the combination of the month January 2024 with year 2023 does not exist and would not be included in the filter. There are, however, situations in which the results are surprising.

Let’s look at an example, where we have a catalog showing availability of colors for products by year. The manufacturer of these products has experimented with making products in various colors throughout the years:

We have three products that have been available in various colors over the years. Notice how there are no red products offered in 2024. That is going to be important a little later.

Now, let’s count the number of products by adding the following measure:Number of Products = COUNTROWS( ‘Catalog’ )

The following matrix shows the number of products that are available in various colors per year:

Now, let’s add another measure to calculate the total number of products for all years:Number of Products All Years = CALCULATE ( [Number of Products], ALL ( ‘Catalog'[Year] ) )

Let’s put these measures side-by-side and filter to year 2023 and just the blue and red colors (no black). As you can see the number of products is 4 and the number of products across all years for these two colors is 6:

If we switch the Year to 2024, we expect the ‘Number of Products’ measure to return 2, as there are just two products that are blue in 2024 and there are no red products in that year.

On top of that, we would expect that the number of products for all years will not change, because, after all, it is supposed to be calculated across all years. However, the ‘Number of Products for All Years’ changes from 6 to 5:

The number of products across all years should still be 6, not 5. What we are seeing here is the value filter behavior in action: it is combining filters on the same table, removing combinations that did not exist. The filters are Year = 2024 and Color = Blue or Red. Since these two filters are on the same table, these filters are combined into one filter that only filters for the combinations that exist. Since there are no red products in 2024, the applied filter is Year = 2024 and Color = Blue.

Therefore, the number of products for all years now counts just the number of blue products, not the blue or red products. This returns 5, as you can confirm in the table.

Influencing the value filter behavior

This month we are giving you control over whether you want this behavior in your semantic model, by using the ‘Value filter behavior’ setting on your semantic model in the properties pane in the model view:

Three options are available:

• Automatic – This is the default setting and currently turns on the Coalesced behavior. When we wrap up this preview, new models set to ‘Automatic’ will use Independent, there will be an announcement at that time.
• Independent – This forces filters on the same table to be kept separate. After setting the ‘Value filter behavior’ setting to ‘Independent’, the total number of products for all years returns 6 as expected (see below).
• Coalesced – This forces the value filter behavior to be enabled for the semantic model and will result in combining the filters on the same table into one. The number of products for all years in our example will continue to return to 5.

The table below shows the impact of this setting to our example:

Value filter behavior settingFilters applied in the exampleResult of example measureAutomaticYear = 2024, Color = Blue5IndependentYear = 2024, Color = Blue or Red6CoalescedYear = 2024, Color = Blue5

Note that setting the ‘Value filter behavior’ to Automatic, means it is equal to Coalesced for now, but will be switched to Independent for new semantic models in the future.

If you set the ‘Value filter behavior’ to Independent, the number of products for all returns 6, as expected, since the filters are Year = 2024 and Color = Blue or Red and are no longer combined:

Come back next week for more interesting insights.