In this guide, you’ll learn how to
toggle between two visual states inside a single chart, using dummy measures and conditional formatting - no bookmarks required.
By the end, you’ll be able to let users click a button to switch between:
- A standard column chart (average rent per city), and
- A box-plot-style view using error bars (min–max and IQR)
Let’s build it.
Step 1: Create the Base Column Chart
Start by plotting the average rent price per city.
Step 2: Add Box Plot Elements Using Error Bars
Box plots need additional series for min–max and inner ranges. Power BI allows
error bars, but only one per series, so we’ll fake additional series using dummy measures.
- Create two dummy measures: Dummy1=0 ; Dummy2=0
- Add them to the same column chart.
- Go to Format > Columns > Layout and set “Series overlap” to 100% to make all columns stack perfectly.
- Turn off data labels for Dummy 1 and Dummy 2.
Step 3: Configure Error BarsFor Dummy 1 (Min–Max Range):
- Add error bars.
- Set Lower Bound to Min Rent.
- Set Upper Bound to Max Rent.
- Remove border.
- Use a light color (e.g., light gray).
For Dummy 2 (Interquartile Range):- Add error bars.
- Set Lower Bound to 25th Percentile.
- Set Upper Bound to 75th Percentile.
- Use a slightly different color (e.g., light blue).
- Increase width and use circular markers.
Step 4: Add a Toggle ControlUse a numeric parameter to let users switch views.
1. Go to Modeling > New Parameter.
2. Create a parameter called Toggle with values 0 and 1.
Min: 0
Max: 1
Increment: 1
3. Add the slicer to your report.
4. In Slicer Settings, enable Single Select and switch to Tile layout.
Step 5: Use DAX to Show/Hide MeasuresUpdate dummy and main measures with logic based on the toggle:
For Dummy 1
Do the same for Dummy 2.
For the Main Column (Average Rent):
Instead of hiding the main chart directly, create a separate measure:
Replace the original measure with this one in the chart.
Step 6: Conditional Axis FormattingSince Power BI doesn’t let us show/hide the y-axis based on state, we trick it with conditional color formatting.
1. Go to Y-Axis > Font color > Conditional formatting.
2. If Toggle = 0 → color = white (hidden)
If Toggle = 1 → color = gray (visible)
Final ResultNow your user can switch between two chart styles -
with just one visual and no bookmarks:- Toggle ON (1): Show Min–Max + IQR ranges using error bars
- Toggle OFF (0): Show simple average rent columns
This method keeps your report lean, avoids bookmark overload, and improves interactivity.
Hope you like it!
Give it a try and see how it works for you! I’d love to hear what you think or see how you use this trick in your own reports.
