DP-500 Question #48: Real Exam Question with Answer & Explanation

Question

You have a Power BI dataset that contains the following measures: - Budget - Actuals - Forecast You need provide users with the ability to use a slicer to switch between the measures in two visuals only. You create a dedicated measure named CG Measure Switch. How should you complete the DAX expression for the Actuals measure? To answer, drag the appropriate values to the targets.

Explanation

The solution requires using DAX's SWITCH and SELECTEDVALUE functions to dynamically return the base Actuals measure when 'Actuals' is selected in the measure switcher slicer.

Approach. The question asks to complete the DAX expression for the 'Actuals' measure within the context of a measure switching mechanism driven by a 'CG Measure Switch' measure. While the phrasing could be interpreted in multiple ways, the standard and most efficient approach for dynamic measure switching in Power BI involves a master measure (like 'CG Measure Switch') that uses a SWITCH statement. This master measure determines which base measure ('Budget', 'Actuals', or 'Forecast') to display based on a user's selection from a slicer (driven by a disconnected table, e.g., 'Measure Selector').

To complete the DAX expression for the 'CG Measure Switch' measure, specifically the part that handles the 'Actuals' selection, two components are required within the SWITCH(TRUE(), ...) statement:

1. Condition: To check if 'Actuals' is the selected value in the slicer. This is achieved using SELECTEDVALUE('Measure Selector'[Measure Name]) = "Actuals". The SELECTEDVALUE function retrieves the single selected value from the 'Measure Selector' table (which contains 'Budget', 'Actuals', 'Forecast' as options), and this result is compared to the string "Actuals".
2. Result: The actual measure to return if the condition is true. This would be the pre-defined base measure [Actuals]. Assuming [Actuals] is already defined as a simple aggregation (e.g., SUM( 'Sales'[ActualsAmount] )).

Therefore, if the question presented drag targets for the condition and result within a SWITCH statement for 'CG Measure Switch', the correct interaction would involve dragging the expression SELECTEDVALUE('Measure Selector'[Measure Name]) = "Actuals" to the condition target and [Actuals] to the result target.

Common mistakes.

• common_mistake. 1. Using SUM(TableName[ActualsColumn]) directly as the result: While this expression might represent the calculation for the base [Actuals] measure, directly embedding it within the SWITCH statement duplicates logic. Best practice dictates defining base measures (like [Actuals], [Budget], [Forecast]) separately as simple aggregations and then referencing them by name within the 'CG Measure Switch' measure. This promotes reusability and easier maintenance.

1. Using only "Actuals" as the result: "Actuals" is a string literal. If dragged as the result, the CG Measure Switch measure would display the text 'Actuals' instead of the actual numerical value of the [Actuals] measure.
2. Incorrect SELECTEDVALUE usage or missing comparison: Omitting SELECTEDVALUE or failing to compare its result to the string "Actuals" (e.g., SELECTEDVALUE('Measure Selector'[Measure Name])) would result in an incorrect condition. This would lead to the 'Actuals' measure not being displayed when selected, or being displayed incorrectly based on the slicer's state.
3. Modifying the base [Actuals] measure directly: If the question implies modifying the base [Actuals] measure itself to include SWITCH logic, it would be an anti-pattern. Base measures should remain simple aggregations. The switching logic belongs in the dedicated 'CG Measure Switch' measure.

Concept tested. Measure branching and dynamic measure selection in Power BI using DAX's SWITCH and SELECTEDVALUE functions. This typically involves a disconnected slicer table to allow users to interactively choose which measure to display in visuals, with a single master measure (CG Measure Switch in this case) handling the conditional logic.

No community discussion yet for this question.