Toggle data values in a slicer

[DioLago79]

Hello all!

I was wondering if there was a way to toggle which data a slicer pulls. For example, if I have two date columns in a table, is there a way to create a single slicer that can switch between those two data sources and not have them concurrently displayed?

If that's not possible, is there a way to make it LOOK like that is what's happening?

Thank you!

[danextian]

Hi @DioLago79 

You can use field parameters to switch between different columns, but when used in a slicer, the selection isn't retained during switching. If the dataset isn't too large and involves only a single query, consider adding an index column to that query. Then reference it, remove unnecessary columns, and keep only the index and date columns. Unpivot the remaining columns based on the index. After that, create a many-to-many single-direction relationship from the index column in the unpivoted table to the index column in the fact table.

Please see the attached sample pbix.

[maruthisp]

Hi DioLago79,
As per my understanding, slicer can't switch between two columns. I tried some workaround to implement the same logic by using DAX.
Please find the attached pbix file with sample data and DAX expressions to achieve the same.
Toggle data values in a slicer.pbix

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated — thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[OwenAuger · Accepted solution]

Hi @DioLago79 

The most direct method to switch the column used on a slicer would be to create a field parameter containing the two possible columns, along with a slicer to select the field parameter value itself.

https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

Depending on the model and the nature of the visuals, you could also use a calculation group approach:

https://www.sqlbi.com/articles/using-calculation-groups-to-switch-between-dates/

With this approach, the slicer would use a column from the date table, say 'Date'[Date], but changing the calculation item selection would change the relationship applied when evaluating measures.

Would either of these methods work for you?

[DioLago79]

Thank you for the succinct response! I ended up using the parameter approach in my scenario, after going through all of the answers I got. It was the easiest in my opinion.