DAXDAX Trainer

DAX Concept

Utilizing Disconnected Slicers for Dynamic Measure Calculations

Learn how to use a disconnected slicer to dynamically influence measure calculations without directly filtering the main data model, and how to apply this to visual titles and conditional formatting.

BeginnerSELECTEDVALUECALCULATEAVERAGEX

Concept Breakdown

A disconnected slicer is a slicer based on a table that has no active relationships with other tables in your data model. This allows the slicer to provide user input without directly filtering the entire model. To utilize the selection from such a slicer in a DAX measure, functions like SELECTEDVALUE or VALUES are used to retrieve the selected item(s), which can then be incorporated into calculations or visual properties like titles. This technique is powerful for 'what-if' analysis or dynamic reporting.

What You Will Learn

  1. Understand when to use SELECTEDVALUE, CALCULATE, AVERAGEX in a Power BI model.

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

  3. Complete a beginner-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 exercise file.

  2. Open the 'Tallest Buildings - Disconnected Slicer.pbix' report.

  3. Import the 'Country Picker' worksheet from the 'Country List.xlsx' file.

  4. In the Model view, verify that the 'CountryPicker' table has no active relationships to any other tables in the model. If an automatic relationship was created with the 'Country' table, delete it.

  5. Add a slicer visual to your report and assign the 'Country Name' column from the 'CountryPicker' table to it. Test that selecting a country in this slicer does not apply any filters to the existing table visual. Set the slicer to allow only a single selection.

  6. Create a new measure named 'Average Height Difference' that calculates the average height of buildings across all countries and then subtracts the average height of buildings for the country name currently selected in the slicer. Use the PROVIDED DAX code for this step.

  7. Create a second measure named 'Dynamic Table Title' that concatenates the phrase "Average building height vs " with the name of the selected country from the 'CountryPicker' slicer. If no country is selected, it should display 'Average Building Height Across All Countries'. (Hint: `VAR _selectedCountryName = SELECTEDVALUE('CountryPicker'[Country Name]) RETURN IF(ISBLANK(_selectedCountryName), "Average Building Height Across All Countries", "Average building height vs " & _selectedCountryName)`)

  8. Add the 'Average Height Difference' measure to the existing table visual and apply conditional formatting (e.g., data bars) to highlight positive and negative values.

  9. Assign the 'Dynamic Table Title' measure to the title property of the table visual. Observe how the title changes as you select different countries in the slicer.

  10. Save and close the report.

Starter DAX

Selected Country = SELECTEDVALUE('CountryPicker'[Country Name])

Expected Outcome

The report will feature a table visual displaying building average heights. When a country is selected in the disconnected slicer, the table's title will dynamically update to reflect the selected country, and a new measure will show the difference between the overall average building height and the average height for the selected country, visualized with conditional formatting.

Checking your sign-in status...