Microsoft Certified: Fabric Analytics Engineer AssociateImplement and manage semantic models (30-35%)Medium
A data modeler is working on a Microsoft Fabric semantic model. The model contains a 'Products' table, a 'Sales' table, and a 'Stores' table. The modeler needs to create a measure that calculates the 'Total Sales' for products that belong to a specific 'Category' and are sold in 'Stores' located in a particular 'Region'. This calculation must be dynamic, allowing users to select different categories and regions in a report. Which DAX function is most appropriate for applying these multiple filter conditions to the 'Sales' table?
- AVALUES
- BRELATEDTABLE
- CSUMX
- DCALCULATE
Show answer & explanationAnswer & explanation
Correct answer: D. CALCULATE
The CALCULATE function is the most powerful and frequently used DAX function for modifying filter context. It allows you to apply new filters or modify existing ones to evaluate an expression, which is essential for dynamic filtering scenarios like this.
Why the other options are wrong
- A. VALUES returns a table of unique values from a column or table; it's used within filter expressions but not as the primary function for applying filters to a measure.
- B. RELATEDTABLE returns a table of rows related to the current row in another table; it's not used for applying broad filter conditions to a measure.
- C. SUMX iterates over a table and sums an expression, but it doesn't directly modify the filter context for multiple conditions across different tables as efficiently as CALCULATE.
CALCULATE Function
The most important function in DAX, which evaluates an expression in a context modified by new filters or removal of existing filters.
- Changes filter context to evaluate an expression.
- Essential for time intelligence, comparisons, and dynamic filtering.
- Can accept multiple filter arguments (tables, columns, boolean expressions).
Memory trick: Calculate to control the context of your data.