Microsoft Certified: Fabric Analytics Engineer AssociateImplement and manage semantic models (30-35%)Hard
A data modeler is creating a semantic model in Microsoft Fabric. The model includes a 'Sales' fact table and a 'Products' dimension table. There is a need to calculate the total sales for 'New Customers' (customers who made their first purchase in the current year) and 'Repeat Customers' (customers who made a purchase in a prior year and also in the current year). The modeler wants to achieve this without adding complex calculated columns to the customer dimension or duplicating the sales measure. Which DAX pattern or feature is most suitable for this scenario?
- ACreating a disconnected slicer table for 'Customer Type' and using `TREATAS`.
- BImplementing Calculation Groups to define customer segments.
- CUsing `CALCULATETABLE` with `SUMMARIZE` to create temporary tables.
- DDefining a measure with nested `CALCULATE` functions and `FILTER` conditions.
Show answer & explanationAnswer & explanation
Correct answer: A. Creating a disconnected slicer table for 'Customer Type' and using `TREATAS`.
A disconnected slicer table for 'Customer Type' (e.g., 'New', 'Repeat') combined with `TREATAS` in DAX allows defining these customer segments dynamically in measures without modifying the base customer dimension. This provides flexibility and avoids complex pre-calculations or redundant measures.
Why the other options are wrong
- B. Calculation Groups are primarily for applying transformations to existing measures (e.g., time intelligence), not for defining new customer segments based on complex logic and then slicing by them.
- C. `CALCULATETABLE` and `SUMMARIZE` are powerful but might lead to more complex measures and are less intuitive for user-driven segmentation than a slicer.
- D. While possible, nested `CALCULATE` and `FILTER` can become very complex and less maintainable than the disconnected slicer pattern for this specific segmentation requirement.
Disconnected Slicer Table with TREATAS
A DAX pattern used in semantic models where a non-related table (disconnected slicer) is used for filtering, and DAX functions like `TREATAS` or `FILTER` with `ALL` are used in measures to dynamically apply the selection from the slicer to the model's data.
- Enables dynamic segmentation and filtering based on custom logic.
- The slicer table is not joined to other tables in the model.
- Provides flexibility for scenarios like 'New vs. Repeat Customers' without altering the base model structure.
Memory trick: Disconnected slicer, TREATAS connects, new and repeat, a DAX perfect reflex.