Read the Jeffery Wang article here.
Filters placed over multiple columns from the same model table end up combining (merging) together in the DAX query as a single column filter. This behavior is called auto-exist(s) and is designed to optimize the SUMMARIZECOLUMNS query. Simply put, the initial existing filtered combinations are bound together and kept together, and other non-existing cross joined combinations between columns are eliminated from the initial query results.
For example, it wouldn’t make sense to include combinations of State = Washington, and City = San Francisco as filters from the same table. They will never exist together by row in the same table nor practically speaking within our report result. Auto-exist correctly steps in to optimize the initial SUMMARIZECOLUMNS query and eliminates these non-existing filter combinations.
However, there could be an example where someone attempts to filter two columns in the same table are not directly related, such as a class subject and a test score from the same fact table. Not best practice, but possible.
Say down the line we decide to write a CALCULATE function that modifies the initial query over the fact table to ignore filters on just one part of the merged (auto-exist) filter value. In this case we are now missing non-existing cross-joined combinations that are now relevant in CALCULATE and now needed for a correct result.
In CALCULATE speak: Auto-exist treats combined filters as an AND condition, compared to column filters from different semantic tables that would generate a cross-joined OR condition.
Up until now, the fix was to stick with best practice of a semantic model to ensure that column filters that could conflict reside in different tables. As of October 2024, a preview feature called Filter Value Behavior now protects us from unexpected results of auto-exist by forcing independent filters even when filtered columns live in the same table.
While the cross-join may be less efficient, it does provide the full query result that will maintain expected results when modified by our CALCULATE measure.
We demonstrate the effect of auto-exist through two reporting examples. The first shows classic auto-exist with unexpected results, followed by an example with the Filter Value Behavior enabled.
Single Table (Auto-exist):
By slicing the values from the single fact table (Right side) on ‘Subject: Accounting and score Under 75%’, we get an unexpected result of 50% given the fact table data shown (far left) in the full report. Mathematically we expect 32%!
I will note that the issue is not the subject or the score classification ’Under 75%’, but rather the score slicer itself creating selected filters over each score in the slicer range.
When subject Accounting is selected in the slicer (establishing the initial query filter) followed by REMOVEFILTERS ignoring subject, the original bound query includes sliced Accounting and only the under 75% scores bound to Accounting (55), of which there are actually two, coincidentally one for finance. This is what generates the result of 110. In this case auto-exist unexpectedly excludes the 60 in Finance.
Click on this sample Auto-Exist report as an example:
Note: Both examples below share the same model, please clear slicers before trying the other side of the report.
1.Filter over separate Dim tables (left report): Accounting and Under 75%: Measure removes filter on the subject dimension column, however when Under 75 remains filtered we get the expected result of 32% – Original query table = cross-join rows of {Accounting, Under 75%, 55 (and 60)} and {Finance, Under 75%, 55 (and 60)}. Ignoring subject leaves {Under 75%, 55 (and 60) } visible in the filter table.
2.Filter a single fact table: (right report): Accounting and Under 75%: Measure removes filter on fact table subject, and we get unexpected results of 50% – Filtering Accounting & Under 75% leaves only the existing merged Accounting score of 55 originally filtered in the slicer. Original query table includes { Accounting, Under 75%, 55 (Context Transition) }, ** Ignoring subject we are left with original filters of {Under 75%, 55(Score)} = without subject we have only two qualified rows filtered 55+55 = 110, when if fact we were expecting three rows! This final percentage of 50% is unexpected!
Single table (Filter Value Behavior):
This next report sets the new model preview feature Filter Value Behavior that forces independent filtering over columns from the same table, thus protecting us from unexpected behavior of auto-exist.
This version returns expected results of 32% regardless of if the columns reside in multiple dim tables (left side) or within the same fact table (right side)!
Click here to see the new result of the above report with Value filter behavior set to Independent:
The feature works well and provides protection over these types of examples; however, this should not be a reason to depart from analyzing data over a traditional semantic model.
Thanks for reading!
Leave a Reply