Using a variable to slice measures in a graph

[gblynch]

I'm trying to make a bar chart that uses measures as values, and I want to encode measures with different variables (1 or 0) that I can multiply the values by so that I can use a slicer to toggle whether or not they appear on the chart. How would I do that effectively, or is there a better way?

[v-sshirivolu · Accepted solution]

Hi @gblynch ,

Thanks for reaching out to the Microsoft fabric community forum.

Create a bar chart that shows multiple measures, but only the selected ones based on a slicer.

1. Create a Disconnected Table with Measure Names

In Power BI, New Table, and create a static list of the measures:

MeasureSelector =
DATATABLE(
"MeasureName", STRING,
{
{"Sales"},
{"Profit"},
{"Cost"}
}
)

This table is not related to your data model.

2. Create a Slicer from the Disconnected Table

• Use the MeasureName column from the MeasureSelector  table as a slicer (set to multi-select).

• Format the slicer as a vertical list or dropdown.
  
  

3. Create a Union Table for Visuals (Optional for Dynamic Axis)

If you want the axis to show measure names, create this table:

ChartAxis =
DATATABLE(
"MeasureName", STRING,
{
{"Sales"},
{"Profit"},
{"Cost"}
}
)

Use  ChartAxis [MeasureName] as the Axis in your bar chart.

4. Create a Measure to Return Values Conditionally

Show Measure Value =
VAR SelectedMeasure = SELECTEDVALUE(ChartAxis[MeasureName])
VAR IsSelected =
CALCULATE(
COUNTROWS(MeasureSelector),
MeasureSelector[MeasureName] = SelectedMeasure
)
RETURN
SWITCH(
TRUE(),
SelectedMeasure = "Sales" && IsSelected, [Sales Measure],
SelectedMeasure = "Profit" && IsSelected, [Profit Measure],
SelectedMeasure = "Cost" && IsSelected, [Cost Measure],
BLANK()
)

Replace [Sales Measure], [Profit Measure], and [Cost Measure] with your actual measures.

5. Build the Visual

• Axis: ChartAxis [MeasureName]

• Values: Show Measure Value

• Slicer: MeasureSelector [MeasureName]

Now select which measures to show using the slicer, and the bar chart will only display bars for the selected ones.

Please Find the attached .pbix file below for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Sreeteja.
Community Support Team 

 

[v-sshirivolu]

Hi @gblynch ,
Just wanted to check if you had the opportunity to review the suggestion provided?

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

[Alex_Sawdo]

If you have the ability to do so (non-direct query data model) you should be able to create a field parameter with your measures so you can dynamically choose which ones to include in your visual. Here's the link to the Microsoft document for field parameters: Use report readers to change visuals (preview) - Power BI | Microsoft Learn