Forum Discussion
Helper IIAdd Measures to Slicer
I have a trend chart tracking categories over time, and each category is scored based on three results. For example, Attendance can be scored 'Above Average, Average, and Below Average'. I would like to be able to use a slicer to view the categories individually on a trend chart. So rather than have one chart for each catgeory, I would have one chart that tracks all categories, and can be filtered using a slicer to show only one category, e.g. "Attendance"
Because of how the data is formatted on the source spreadsheet, I use measures to be able to seperate the categories in Power BI, but I cannot use the measures in a slicer. I've looked at some solutions online, but all of them approach it as displaying the results in a table, not a chart, and most only use two results (Higher/Lower) as an example, whereas I have three.
The below shows one trend chart showing one category with three measures, and one trend chart showing two categegories with six measures.
Any advice is welcome thank you
You can not use a measure in this case. The values must be placed in a column of a table in the model:
If your data can't be easily unpivoted, you can use a disconnected table and switched measures.- Create a Disconnected Category Table:
- Go to Modeling > New Table.
- Use DAX like this (replace with your actual categories):This creates a simple table with one column: Category.
Category Table = DATATABLE( "Category", STRING, {{"Attendance"}, {"Performance"}, {"Other Category"}} )
- Create the Slicer:
- Drag "Category" from the new table to the canvas as a slicer.
- Set to single-select if desired.
- Create Switched Measures for Each Result:
- Assuming you already have per-category measures (e.g., [Attendance Above], [Attendance Average], [Attendance Below], etc.), create three dynamic measures:
Above Dynamic = SWITCH( SELECTEDVALUE('Category Table'[Category]), "Attendance", [Attendance Above], "Performance", [Performance Above], "Other Category", [Other Category Above], BLANK() // Default if no selection )
Average Dynamic = SWITCH( SELECTEDVALUE('Category Table'[Category]), "Attendance", [Attendance Average], "Performance", [Performance Average], "Other Category", [Other Category Average], BLANK() )
Below Dynamic = SWITCH( SELECTEDVALUE('Category Table'[Category]), "Attendance", [Attendance Below], "Performance", [Performance Below], "Other Category", [Other Category Below], BLANK() )
- Assuming you already have per-category measures (e.g., [Attendance Above], [Attendance Average], [Attendance Below], etc.), create three dynamic measures:
- Create the Trend Chart:
- Add a Line Chart.
- Axis: Date/Time Period.
- Values: Add [Above Dynamic], [Average Dynamic], [Below Dynamic] (they'll appear as separate lines/series).
- The chart will update based on the slicer selection.
This method is quick if you have few categories but can become cumbersome with many (long SWITCH statements). To handle "no selection," you could wrap in IF(HASONEVALUE(...), SWITCH(...), [Some Default Measure]).
I hope this helps. if so please mark it as a solution. kudos are welcome.- Create a Disconnected Category Table:
7 Replies
- pcoley
Super User
You can not use a measure in this case. The values must be placed in a column of a table in the model:
If your data can't be easily unpivoted, you can use a disconnected table and switched measures.- Create a Disconnected Category Table:
- Go to Modeling > New Table.
- Use DAX like this (replace with your actual categories):This creates a simple table with one column: Category.
Category Table = DATATABLE( "Category", STRING, {{"Attendance"}, {"Performance"}, {"Other Category"}} )
- Create the Slicer:
- Drag "Category" from the new table to the canvas as a slicer.
- Set to single-select if desired.
- Create Switched Measures for Each Result:
- Assuming you already have per-category measures (e.g., [Attendance Above], [Attendance Average], [Attendance Below], etc.), create three dynamic measures:
Above Dynamic = SWITCH( SELECTEDVALUE('Category Table'[Category]), "Attendance", [Attendance Above], "Performance", [Performance Above], "Other Category", [Other Category Above], BLANK() // Default if no selection )
Average Dynamic = SWITCH( SELECTEDVALUE('Category Table'[Category]), "Attendance", [Attendance Average], "Performance", [Performance Average], "Other Category", [Other Category Average], BLANK() )
Below Dynamic = SWITCH( SELECTEDVALUE('Category Table'[Category]), "Attendance", [Attendance Below], "Performance", [Performance Below], "Other Category", [Other Category Below], BLANK() )
- Assuming you already have per-category measures (e.g., [Attendance Above], [Attendance Average], [Attendance Below], etc.), create three dynamic measures:
- Create the Trend Chart:
- Add a Line Chart.
- Axis: Date/Time Period.
- Values: Add [Above Dynamic], [Average Dynamic], [Below Dynamic] (they'll appear as separate lines/series).
- The chart will update based on the slicer selection.
This method is quick if you have few categories but can become cumbersome with many (long SWITCH statements). To handle "no selection," you could wrap in IF(HASONEVALUE(...), SWITCH(...), [Some Default Measure]).
I hope this helps. if so please mark it as a solution. kudos are welcome.
Lucy01This worked! Thanks for your help
I have the situation where i am 16 different options in my slicer, ideally i'd not want to make a measure per option, how would i formulate this?
- Ashish_Mathur
Hi,
If you have to use the same calculation for each measure, then just create 1 measure and then use field parameters to select a measure from the slicer.
- Create a Disconnected Category Table:
Hans-Georg_PulsHi Lucy01,
I'm not sure if I understand your requirements exactly but that sounds like a job for field parameters to me. Have you ever worked with field parameters? The following steps would be necessary for this:
- Modeling -> New parameter -> Fields
- Add all measures that should belong to your slicer
- Leave the "Add slicer to this page" option selected if you want Power BI to generate a slicer for you.
- Add the only column of the newly generated fields parameter table to the y-axis of your line chart.
Does that already help you?
If not, could you pleaseprovide some more details about your data model, about what is assigned to x-axis, y-axis and legend fields of the line chart and how your measures look like?
- cengizhanarslan
1) Go to Modeling → New parameter → Fields
2) Add the measures you want to toggle, for example:
- [Attendance Above]
- [Attendance Average]
- [Attendance Below]
- [Classwork Above]
- [Classwork Average]
- [Classwork Below]
3) Power BI will create:
- a parameter table
- a parameter field
4) Add the generated parameter field to:
- a Slicer
- the line chart Values (instead of adding measures manually)
If you want to group measures as Attendance | Classwork or Above | Average | Below, then forget the logic above and follow the logic below:
Step 1) Create a disconnected category table
Category Selector = DATATABLE( "Category", STRING, { {"Attendance"}, {"Classwork"} -- add more categories } )Step 2) Add slicer to report
-
Add Category Selector[Category] to a slicer
-
Turn Single Select = On (recommended for UX)
Step 3) Create a helper measure (selected category)
Selected Category = SELECTEDVALUE('Category Selector'[Category])Step 4) Create wrapper (“visible”) measures
Attendance Above (Visible) = IF( [Selected Category] = "Attendance", [Attendance Above], BLANK() ) Attendance Average (Visible) = IF( [Selected Category] = "Attendance", [Attendance Average], BLANK() ) Attendance Below (Visible) = IF( [Selected Category] = "Attendance", [Attendance Below], BLANK() ) Classwork Above (Visible) = IF( [Selected Category] = "Classwork", [Classwork Above], BLANK() )Use wrapper measures in the chart.
