Forum Discussion
Add Measures to Slicer
- 7 months ago
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:
Hi 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?