Forum Discussion
Add Measures to Slicer
- 6 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:
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.
This worked! Thanks for your help