DAXDAX Trainer

DAX Concept

Implementing Dynamic Unit Scaling for Financial Measures with Slicers

Learn to create dynamic measures that adjust their output based on a single selection in a slicer, using `HASONEVALUE` and `VALUES` to retrieve the selected unit's divisor. Also, create a conditional title based on slicer selection.

IntermediateHASONEVALUEVALUESDIVIDEIFBLANK

Concept Breakdown

`HASONEVALUE` is a DAX function that checks if there is exactly one distinct value in a specified column within the current filter context. This is crucial for creating interactive reports where measures need to react differently when a single item is selected in a slicer versus when multiple items or no items are selected. When `HASONEVALUE` returns TRUE, `VALUES(<column>)` can then be safely used in a scalar context to retrieve that unique selected value, enabling dynamic calculations like unit conversion.

What You Will Learn

  1. Understand when to use HASONEVALUE, VALUES, DIVIDE, IF, BLANK in a Power BI model.

  2. Practice the concept inside a real PBIX report rather than only reading syntax.

  3. Complete a intermediate-level task and verify the result with a hidden answer check.

Practice in Power BI

The starter PBIX link has not been attached yet. The student workflow is ready for the admin to connect the file.

  1. Download and unzip the provided Power BI starter file.

  2. Open the Power BI file and observe the existing table displaying average box office takings, budget, and profit by film certificate.

  3. Import the `Units` worksheet from the provided Excel file into your Power BI model. Ensure this new table contains at least two columns: `Unit` (e.g., "$", "$k", "$m") and `Divisor` (e.g., 1, 1000, 1000000).

  4. Add a Slicer visualization to your report page, using the `Unit` column from the newly imported `Units` table.

  5. Create a new measure called `Average Box Office (Dynamic)`. This measure should use `HASONEVALUE` and `VALUES` to retrieve the `Divisor` corresponding to the unit selected in the slicer. It should then divide the existing `AVERAGE(Films[Box Office])` by this divisor. If no single unit is selected in the slicer, the measure should return BLANK.

  6. Repeat step 5 to create two similar dynamic measures: `Average Budget (Dynamic)` and `Average Profit (Dynamic)`, applying the same logic to their respective base measures.

  7. Create an additional measure called `Title`. This measure should display "Unit chosen: " followed by the name of the selected unit if a single unit is chosen in the slicer. If no unit or multiple units are chosen, it should display "No unit chosen".

  8. Add a Card visualization to your report page and display the `Title` measure.

  9. Replace the original Average Box Office, Budget, and Profit measures in your table with their new dynamic counterparts.

  10. Test the report by selecting different units in the slicer. Observe how the figures in the table and the text in the `Title` card change dynamically.

  11. Save your Power BI file.

Starter DAX

Average Box Office = AVERAGE( 'Films'[Box Office] )

Expected Outcome

When a single unit (e.g., '$m') is selected in the slicer, the Average Box Office, Budget, and Profit figures in the table will dynamically divide by the corresponding divisor (e.g., 1,000,000). If no unit or multiple units are selected, these measures will display BLANK. The 'Title' card will dynamically show 'Unit chosen: $m' (or the selected unit) when a unit is picked, and 'No unit chosen' otherwise.

Checking your sign-in status...