Microsoft Certified: Power BI Data Analyst AssociateModel the dataEasy
A data modeler is designing a Power BI model for a retail chain. The model includes a 'Sales' table (Fact) and a 'Products' table (Dimension). The 'Products' table contains a 'CategoryID' column, and the 'Sales' table contains a 'ProductID' column. There is a one-to-many relationship between 'Products[ProductID]' and 'Sales[ProductID]'. The modeler needs to calculate the total sales for a specific category, regardless of any other filters applied to the 'Products' table, except for the category filter itself. Which DAX function should be used to achieve this?
- AALLEXCEPT
- BKEEPFILTERS
- CALLSELECTED
- DALL
Show answer & explanationAnswer & explanation
Correct answer: A. ALLEXCEPT
The ALLEXCEPT function removes all context filters from the specified table except for filters that have been applied to the specified columns. This allows for calculating totals while preserving specific column filters.
Why the other options are wrong
- B. KEEPFILTERS modifies how filters are applied in CALCULATE, but it does not remove existing filters from a table based on column exceptions.
- C. ALLSELECTED removes filters from the current query but retains explicit filters and contexts, which is not suitable for removing all but specific column filters.
- D. ALL removes all context filters from a table or all filters from specified columns, which would remove the desired category filter.
ALLEXCEPT Function
A DAX function that removes all context filters from a table except for those filters that have been applied to the specified columns.
- Used within CALCULATE to modify filter context.
- Preserves filters on specified columns.
- Removes all other filters from the table.
Memory trick: Always Except the ones you want to keep.