Toggle between columns used in a date slicer

[aallman]

Hello hopefully this is pretty straightforward.

I am wondering if it is possible to have a date slicer on my page, but allow the user to toggle between two dates. For example, I have order date and due date. I want to have just one date slicer on the page, but allow the user to select "Order Date" or "Due Date" from another slicer. Based on their selection, I want the date slicer to use the appropriate date field. Is that possible? I have looked into a slicer table and then a measure, but in the measure I am not sure how to return the date values to use in a slicer, only to return calculated values such as counts and totals.

[OwenAuger · Accepted solution]

Hi @aallman 

I would recommend using the method described here:

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

This article includes a video and sample files.

In your case, this method would involve:

• Date table with one active relationship and one inactive relationship with your fact table (on the Order Date and Due Date columns).
• Calculation group with two calculation items named corresponding to these two dates, that allows selection of which relationship to activate.
• Slicer to select between the calculation items.

With this method, the 'Date'[Date] columnis always used on the Date slicer, but the column that it has an active relationship with is switched using the calculation group.

Does something like this work for you? Please post back with more detail if needed.

P.S. Field parameters are another option for switching the column used in a slicer or an other visual, but I would say that the calculation group makes better sense from a modelling point of view in this case.