Create Dynamic Measures in Power BI Using a Role Toggle
Introduction
Have you ever wondered if you could display two entirely different calculations in the same visual dynamically by selecting a value in a slicer? Imagine a scenario where you’re building a demographics dashboard for club enrolment. You want to display both Student and Mentor demographic breakdowns within the same visuals, and switch between them effortlessly with a single slicer selection.
In this blog, I’ll walk you through exactly how to make that happen in Power BI. We’ll build a dynamic measure using a disconnected table that lets users toggle views, keeping your report streamlined and intuitive.
Understanding the Data Model
The model is centred around a fact table called Club Membership, which captures each student’s enrolment into various clubs. This table connects to three key dimension tables:
- Student Details – contains demographic info for each student
- Mentor Details – holds demographic info for each mentor
- Clubs – a simple lookup of club names
Demographics are provided via three shared lookup tables: Age, Gender, and Ethnicity, which are:
- Actively related to Student Details, enabling straightforward filtering of student data
- Inactively related to Mentor Details, requiring
USERELATIONSHIPto activate when needed
Finally, we add a disconnected Role Selector table with two values: “Student” and “Mentor.” This table doesn’t participate in any relationships—it simply powers a slicer that drives our dynamic logic.
Step 1: Create a Role Selector Table
To create a dynamic toggle between Student and Mentor views, we need a disconnected table, a table with no physical relationships to the rest of the data model. Its sole purpose is to act as a driver for dynamic logic in our measures.
Here’s how you can set it up:
1. Open Power BI Desktop
Launch your report and go to the Modelling tab.
2. Use New Table to Create the Role Selector
Click on “New Table”, and enter the following DAX:
RoleSelector = DATATABLE("Role", STRING, {{"Student"}, {"Mentor"}})
This creates a simple two-row table with just one column Role containing the values Student and Mentor.
3. Add the Role Selector as a Slicer
Drag the Role column into a slicer visual on your report canvas. You can format it as a dropdown or buttons, depending on your design preference and set it to single select.
📌 Important: Do not connect this table to any other table in the data model. It remains intentionally disconnected so that we can use it purely in our DAX logic without introducing unwanted filters.
This step sets the foundation for building clean, controlled dynamic measures based on user selection. It keeps your logic flexible while ensuring your data relationships stay optimised.
Step 2: Create a Dynamic Measure for Counts
- This line captures the role selected by the user from the
RoleSelectorslicer. SELECTEDVALUE()Returns the single selected value—either"Student"or"Mentor".- If nothing or multiple values are selected, it returns blank, which is handled later in the logic.
SWITCH()is used to return different logic based on the value ofSelectedRole.- It's similar to an
IF...ELSE IF...structure, but cleaner and more readable for this use case.
- If
"Student"is selected, the measure returns a distinct count ofStudent IDfrom theClub Membershiptable. - This gives you the number of unique students enrolled in clubs.
- If
"Mentor"is selected, it instead returns the distinct count ofMentor IDfrom the same table. - So, same visual, different metric—switching based on role
- This acts as a fallback—if nothing is selected (or an unexpected value somehow ends up in the slicer), the measure returns blank.
- It keeps things tidy and avoids misleading results.
Step 3: Build Role-Aware Demographic Visuals
Let’s take a deeper look at how to build visuals that break down counts by demographics like Ethnicity and respond correctly for both Students and Mentors:
Dynamic Count by Ethnicity =
VAR SelectedRole = SELECTEDVALUE(RoleSelector[Role])
RETURN
SWITCH(
SelectedRole,
"Student",
CALCULATE(
DISTINCTCOUNT('Club Membership'[Student ID]),
VALUES('Ethnicity'[Ethnicity])
),
"Mentor",
CALCULATE(
DISTINCTCOUNT('Club Membership'[Mentor ID]),
USERELATIONSHIP('Ethnicity'[Ethnicity], 'Mentor Details'[Ethnicity]),
VALUES('Ethnicity'[Ethnicity])
)
)
Create the measure as discussed earlier and use a tree map visual from the visualisation pane, drag Ethnicity from the Ethnicity lookup table into the Category and the measure into the values.
What Each Piece Does:
VAR SelectedRole = SELECTEDVALUE(RoleSelector[Role])
Captures the user’s selection from the disconnected slicer, either “Student” or “Mentor”.SWITCH(...)
Branches logic based on which role was selected."Student"Block:
FiltersClub Membershipby each ethnicity in the visual (i.e., row context from the chart’s axis), and counts distinct Student IDs accordingly."Mentor"Block:
Activates the inactive relationship betweenEthnicityandMentor DetailsusingUSERELATIONSHIP, applies the same ethnicity filtering, and returns the distinct count of Mentor IDs.VALUES('Ethnicity'[Ethnicity])
Ensures row-level calculation per ethnicity shown in the chart—even in the absence of a slicer—by respecting the row context of the visual axis.
VALUES('Ethnicity'[Ethnicity]) is NeededEven though there’s no slicer, the Ethnicity field is used in the visual axis or legend (like in a bar chart or pie chart). That visual inherently produces multiple filter contexts, one for each Ethnicity group.
When you write:
VALUES('Ethnicity'[Ethnicity])
You’re telling Power BI:
“For each individual Ethnicity value in this visual, evaluate the distinct count of IDs within that Ethnicity context.”
Without that line, you’d get a single total for the entire visual, not a breakdown by group.
However, here in this case. Because we are using the Ethnicity field directly from the lookup table in the visual’s axis, and the lookup table is connected in the model (even if only via inactive relationship for mentors), Power BI will still generate a filter context for each ethnicity value automatically. That row context is applied implicitly by the visual, meaning your measure can still iterate per group even without VALUES('Ethnicity'[Ethnicity]).
So yes, functionally it should still work without explicitly adding the VALUES(...) line. Including it just makes that intent explicit; some developers prefer to define the row context within CALCULATE to avoid ambiguity, especially when more complex filters or layered conditions are involved. I am one of them.
Repeating the Pattern for Gender and Age
Step 4: Testing It Out.
Why SWITCH(TRUE())?
Normally, SWITCH() works like this:
SWITCH( <expression>, <value1>, <result1>, <value2>, <result2>, ... )
But that only works for matching exact values.
When you want to evaluate logical conditions (like RoleCount = 2 or a combo of conditions), SWITCH(TRUE(), ...) flips the pattern:
- It says: “Find the first expression that evaluates to TRUE, and return its corresponding result.”
- So it becomes like a cleaner, more readable
IF...ELSE IF...ELSEcascade.
FILTER(...)ensures each variable always returns a valid table, even if zero rows match.- We wrap with
DISTINCT(...)in the final count, just in case any IDs overlap. - We avoid
IF()altogether, which simplifies logic and eliminates branching errors.
This gives you a clean, bulletproof way to dynamically merge distinct IDs and count them, based on any combination selected in the slicer.
Wrapping Up
Dynamic measures, when paired with a disconnected slicer, open up powerful and user-friendly ways to present data in Power BI. In this blog, we explored how to toggle between entirely different entities, Student and Mentor, within the same visuals, and even support combined views with unified logic. We walked through constructing a flexible DAX using SWITCH(TRUE()) for scalar values and transitioning into table logic with UNION() and FILTER() for more advanced cases.
Whether you’re working with demographic breakdowns or building adaptable dashboards for multi-role analysis, this pattern helps you stay lean with your visuals while creating a seamless user experience.
.jpg)
Comments
Post a Comment