Use Switch to change the date based on measure outcome

[Knato]

Hello!

I am wondering, if it is possible to create a some sort of SWITCH function, where based on the value from a measure, it will select the respective Date Name from the date hierarchy.

I have a slicer, which is connected to a Calendar, that calendar is then used in all the other metrics. So if I select 2020 it will only display the data from 2020. If I further select month, it will show the data from the selected month and 2020.

What I want to achieve, is that when I click on a year, automatic drill down will be applied to the other metric, and month will be shown there - without manually having to click it.

I made this metric, which outputs 'Year' or 'Month' or 'Day' based on the number of being currently aviable based on the slicer filter. THis works great - If I have year selected the output is 'Month' and if I have year and month selected the output is 'Day'

The final step, where I am not sure how to do this, would be to use this value and apply the date hierarchy in the other metric in it.

I tried using a Switch function, so if the result is 'Month' then use the Month from the date hierarchy but I cannot get it to work.

Please, is there any way I can achieve this?

Thank you.

[johnt75 · Accepted solution]

Have a look at https://www.youtube.com/watch?v=KReYWx5NXYg

[Knato]

this worked, thanks!