Using ALLEXCEPT vs ALL VALUES
Data Architect specializing in modern analytics platforms across banking, education, and enterprise environments. Designing scalable lakehouse architectures with Microsoft Fabric, Azure, Databricks, Snowflake, and dbt, with strong expertise in Power BI, semantic modeling, DAX, and Power Query.
Focused on building secure, high-performance, governed data platforms that enable real-time intelligence and self-service analytics, while exploring how GenAI and Azure AI bring practical intelligence into everyday analytics.
Summary
The document discusses the DAX functions ALLEXCEPT, ALL, VALUES, and REMOVEFILTERS, specifically in the context of data filter manipulation. It highlights the differences between using ALLEXCEPT and combinations of ALL and VALUES, emphasizing that while ALLEXCEPT can be useful, it carries risks of incorrect calculations when certain filters are absent. The piece also introduces best practices such as the preferred approach with REMOVEFILTERS/VALUES for more reliable results and offers examples to illustrate these concepts.
Key Points
Overview of DAX Functions
DAX functions in the ALL family, including ALLEXCEPT, can behave as table functions or CALCULATE modifiers.
ALLEXCEPT is primarily used with CALCULATE to maintain specific column filters while removing others.
ALLEXCEPT vs. ALL and VALUES
Two patterns for removing filters:
UsingAllExcept := CALCULATE ( [Sales Amount], ALLEXCEPT (Customer, Customer[Continent] ) )UsingAllValues := CALCULATE ( [Sales Amount], ALL ( Customer ), VALUES ( Customer[Continent] ) )
Despite similar appearances, the methods yield different results due to ALLEXCEPT's sensitivity to external filter contexts.
Introduction of REMOVEFILTERS
REMOVEFILTERS was introduced in 2019 as an alias for ALL, enhancing code readability.
Example of using REMOVEFILTERS:
UsingRemoveFiltersValues := CALCULATE ( [Sales Amount], REMOVEFILTERS ( Customer ), VALUES ( Customer[Continent] ) )
Application and Risks of ALLEXCEPT
An example is provided for calculating the percentage of sales by country against continent totals, illustrating the expected behavior with ALLEXCEPT.
ALLEXCEPT can lead to fragile measures: if the relevant filter (e.g. Continent) is removed from the report, it may result in inaccurate calculations.
Understanding Filter Context
When using ALLEXCEPT, if there are no applicable filters, it doesn’t introduce new filters, potentially leading to empty contexts.
The default behavior of ALLEXCEPT is confirmed through examples where filter contexts shift based on report configurations.
Recommendations for Safer Measures
Recommended to use REMOVEFILTERS/VALUES for retaining filters on specific columns as it is more reliable:
Example for accurate percentage calculation:
PercOverContinent := ...using REMOVEFILTERS and VALUES effectively retains necessary filters.
Further Functionality with VALUES and SUMMARIZE
VALUES can be used to retain filters on a single column, while SUMMARIZE is suggested for multiple columns.
Example to compute a city’s percentage against state sales, retaining filters on multiple columns.
Conclusion
Understanding how DAX functions manipulate filter contexts is crucial for creating robust code.
While ALLEXCEPT has its place, best practices favor the combination of REMOVEFILTERS/VALUES for accurate calculations across different report setups.
Code Used
Live Report
Tips to Remember
*VALUES function is not a calculate modifier. It can bring back the values that are related to filter context.
