Forum Discussion
Advocate IIHow to filter a slicer dynamically based on selection on field parameter
Hi,
I have a report where there are multiple different measures. The user can choose from field parameter slicer which measures they want to see. I also have a slicer for department, which uses a dimension table with a relationship to another table that is used in calculating the measures. Not all departments have all of the measures so I'd need to filter the department slicer based on the field parameter slicer. So if user selects measure1 the department slicer only shows the departments that are relevant to the measure1 (or include data relevant to the measure).
Is this possible?
Add below Measure (Show Department) and apply this as a visual filter to Department Slicer (DepName) -> Show Department = "1"
Show Department = VAR _SelectedMeasure = SELECTEDVALUE ( 'Parameter'[Parameter Fields] ) RETURN SWITCH ( TRUE(), _SelectedMeasure = NAMEOF([Calc1]), IF ( NOT ISBLANK ( [Calc1] ), 1 ), _SelectedMeasure = NAMEOF([Calc2]), IF ( NOT ISBLANK ( [Calc2] ), 1 ), 1 )Result -
💡 Helpful? Give a Kudos 👍 — keep the community growing
✅ Solved your issue? Mark as Solution ✔️ — help others find it faster
Best regards,
Rupasree Achari | BI & Fabric Analytics Engineer
9 Replies
- Kedar_Pande
Super User
Add a measure that flags whether each department has data for the selected measure, then filter the slicer by it
Dept Has Data = IF(NOT ISBLANK([Selected Measure Value]), 1, 0)
Put it in the department slicer's filter pane, show items where = 1. Switching the field parameter drops departments with no data for that measure.
If this answer helped, please click 👍 or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande - Rupa01
Solution Sage
Hi Petja,
You can achieve this by making the Department slicer dynamically filter based on the selected Field Parameter value. Create below measure that checks whether the selected department has data for the currently selected measure, then use that measure as a visual-level filter on the Department slicer.
VAR _SelectedMeasure = SELECTEDVALUE ( 'Parameter'[Parameter Fields] ) RETURN SWITCH ( TRUE(), _SelectedMeasure = NAMEOF([Measure 1]), IF ( NOT ISBLANK ( [Measure 1] ), 1 ), _SelectedMeasure = NAMEOF([Measure 2]), IF ( NOT ISBLANK ( [Measure 2] ), 1 ), 1 )Add Department to the slicer > Add Show Department to the slicer's Visual Level Filters > Filter the measure to equals 1
As a result, when a user selects Measure1 from the Field Parameter slicer, the Department slicer will only display departments that return data for Measure1. Changing the selected measure automatically updates the available departments.
💡 Helpful? Give a Kudos 👍 — keep the community growing
✅ Solved your issue? Mark as Solution ✔️ — help others find it faster
Best regards,
Rupasree Achari | BI & Fabric Analytics Engineer- Rupa01
Petja, check out below eaxmple using sample data -
Sample Data -
Measures Parameter interacting dynamically with Department Slicer -
Measure 1 is SUM(Sales) and Measure 2 is SUM(Revenue)
💡 Helpful? Give a Kudos 👍 — keep the community growing
✅ Solved your issue? Mark as Solution ✔️ — help others find it faster
Best regards,
Rupasree Achari | BI & Fabric Analytics Engineer
PetjaHi,
Thanks, I guess this will work if I create a summary table. But right now my measures are based on different filter contexts and not different columns and creating multiple dax calculated columns will surely make the report sluggish.
