Pär Adeen on 16 Nov 2024 07:18:18
Proposal: Implement an implicit filter removal mechanism in Power BI that automatically removes filters on dependent sorting columns when a filter is removed from the primary column. This behavior should respect any explicit filter modifiers applied by the user to the sorting column.
Details:
Implicit Filter Removal: When a filter modifier such as ALL, ALLSELECTED, or REMOVEFILTERS is applied to a primary column, Power BI should automatically remove filters on any columns that the primary column is sorted by.
Respect Explicit Modifiers: If the user explicitly includes or excludes the sorting column in the filter modifier, Power BI should respect this explicit instruction and not override it.
Example
Data Model Setup
Table: Products
Columns:
Category: The primary column containing product categories.
Category_sort: A column used to define the sort order for Category.
Sales: The sales amount for each product category.
Sorting Configuration: In the data model, the Category column is configured to be sorted by the Category_sort column.
Table Data
Category Category_sort Sales
A 1 1
B 2 2
C 3 4
Current Measure
Measure =
CALCULATE(
SUM(Products[Sales]),
ALL(Products[Category])
)
Expected Behavior with Implicit Filter Removal: The measure should automatically remove filters from both Category and Category_sort, resulting in:
IsGrandTotalRowTotal Category Category_sort SumSales Measure
1 (blank) (blank) 7 7
0 A 1 1 7
0 B 2 2 7
0 C 3 4 7
Benefits
Simplified DAX Code: Users wouldn’t need to remember to include both the primary column and its sort column in filter modifiers, reducing the complexity of the DAX code.
Reduced Errors: It would prevent scenarios where the measure produces unexpected results due to the sort column still being filtered, thus reducing potential errors.
Intuitive Behavior: The measure would behave more intuitively, aligning with what users typically expect when they remove a filter from a primary column.
Model Stability: If someone adds a sorting column to the model after the measures have been verified, the implicit filter removal ensures that the behavior of the model remains consistent. This prevents unexpected changes in measure results due to new sorting columns, maintaining the stability and reliability of the model.
Conclusion
Implementing implicit filter removal for dependent sorting columns would make DAX measures more intuitive and reduce the likelihood of unintended side effects. This approach would simplify the user experience, ensure consistent behavior, and maintain the stability of the model even when changes are made, e.g. changing the sorting of a column