Atvik
Heimsmeistaramót Power BI DataViz
Feb 14, 4 PM - Mar 31, 4 PM
Með 4 tækifæri til að taka þátt gætirðu unnið ráðstefnupakka og komist í LIVE Grand Finale í Las Vegas
Nánari upplýsingarÞessi vafri er ekki lengur studdur.
Uppfærðu í Microsoft Edge til að nýta þér nýjustu eiginleika, öryggisuppfærslur og tæknilega aðstoð.
The value filter behavior options in Power BI allow you to influence the automatic filtering mechanism present in DAX that occurs when multiple columns from the same table are filtered. This behavior is informally called 'auto-exist'.
When multiple columns from the same table are filtered, DAX understands that likely 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. The value filter behavior setting enables to you change 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 later in this article) results in multiple filters on the same table being kept separate instead of the DAX engine combining these filters into one.
When you're 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:
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. The combination of the month January 2024 with year 2023 doesn't exist and wouldn't 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 experimented with making products in various colors throughout the years:
We have three products that were available in various colors over the years. Notice how there are no red products offered in 2024. This 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 (so no black). 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 won't change, because, after all, it's 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's the value filter behavior in action: it's combining filters on the same table, removing combinations that didn't 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.
You can control 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:
The following table shows the effect of this setting to our example:
Value filter behavior setting | Filters applied in the example | Result of example measure |
---|---|---|
Automatic | Year = 2024, Color = Blue |
5 |
Independent | Year = 2024, Color = Blue or Red |
6 |
Coalesced | Year = 2024, Color = Blue |
5 |
Setting the Value filter behavior to Automatic, means it's 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:
The following articles may be useful:
Atvik
Heimsmeistaramót Power BI DataViz
Feb 14, 4 PM - Mar 31, 4 PM
Með 4 tækifæri til að taka þátt gætirðu unnið ráðstefnupakka og komist í LIVE Grand Finale í Las Vegas
Nánari upplýsingarÞjálfun
Eining
Modify DAX filter context in Power BI Desktop models - Training
By the end of this module, you'll be able to describe filter context, which is used to evaluate measure formulas. You'll then learn why some calculations need to modify filter context and that it can be achieved by using the CALCULATE function. Additionally, you'll learn how to configure the CALCULATE function with filters and filter modifiers.
Vottorð
Microsoft Certified: Power BI Data Analyst Associate - Certifications
Demonstrate methods and best practices that align with business and technical requirements for modeling, visualizing, and analyzing data with Microsoft Power BI.