Forum Discussion
How to Automatically Reset Slicer Selections When Changing value in Slicer in Power BI?
One possible workaround is similar to the method described here:
https://exceleratorbi.com.au/slicer-values-dont-clear-after-changing-a-second-slicer/
Caveats:
- This method only makes sense when single selections are made on the first slicer.
- It also requires copies of the columns used on the slicers that have to reset (the 2nd & 3rd slicers in this example), which are then linked to field parameter columns added to the table containing the column used on the first slicer.
I have set up an example using your PBIX (attached).
Setup steps:
1. Add these columns to From_EVM_Dates, all equal to From_EVM_Dates[週].
One column must be added for each distinct SprintKey value (three in this case):
週 1 =
From_EVM_Dates[週]
------------------
週 2 =
From_EVM_Dates[週]
------------------
週 3 =
From_EVM_Dates[週]
(This can be automated by constructed tables in Power Query but the sample uses DAX calculated columns).
2. Add these columns to To_EVM_Dates, all equal to To_EVM_Dates[週].
One column must be added for each distinct SprintKey value (three in this case):
週 1 =
To_EVM_Dates[週]
------------------
週 2 =
To_EVM_Dates[週]
------------------
週 3 =
To_EVM_Dates[週]
(This can be automated by constructed tables in Power Query but the sample uses DAX calculated columns).
3. Add these two columns to Project_EVM (just one possible way of defining these columns):
From 週 Column =
VAR SprintKeyRank =
RANK ( DENSE, DISTINCT(Project_EVM[SprintKey]) , ORDERBY ( Project_EVM[SprintKey] ) )
RETURN
"From_EVM_Dates[週 " & SprintKeyRank & "]"
-----------------------------------------------------------------------------------------
To 週 Column =
VAR SprintKeyRank =
RANK ( DENSE, DISTINCT(Project_EVM[SprintKey]) , ORDERBY ( Project_EVM[SprintKey] ) )
RETURN
"To_EVM_Dates[週 " & SprintKeyRank & "]"
4. Using Tabular Editor (or TMDL view), add a JSON Extended Property to each of the columns from step 3, equal to {"version":3, "kind"2}. These two columns will then be recognised as field parameter columns.
5. Change the 2nd & 3rd slicer fields to Project_EVM[From 週 Column] and Project_EVM[To 週 Column].
In the field context menu, select "Show values of selected field":
6. Now changing the SprintKey slicer selection will reset the 2nd & 3rd slicers.
Note that making no selection or multiple selections on the SprintKey slicer would produce an unwanted hierarchy on the other two slicers.
Would something like this work for you?
Thank you for your detailed responses!
However, I am wondering: if SprintKey is increases, does that mean I need to manually create the corresponding 週 (number) in From_EVM_Dates and To_EVM_Dates?
- OwenAuger9 months agoSuper User
Amyries Good point! The need for a potentially growing set of copies of these columns is the main drawback with this method.
I consider this method a workaround and certainly not good modelling practice. Model table schemas should generally not change over time.
I would only recommending using this method if you can automate the additional columns in Power Query or further upstream. I wouldn't recommend adding these columns via DAX in practice.
Also, it may not be desirable to use this method if the number of SprintKey values becomes particular large.