Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Knato
New Member

Use Switch to change the date based on measure outcome

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.

Knato_0-1668001398800.png

Knato_1-1668001520100.png

 

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.

 

Knato_2-1668001644300.png

 

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'

 

Knato_3-1668001712400.png

 

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.

 

Knato_4-1668001831500.png

 

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.

Knato_6-1668001896500.png

 

 

 

 

Please, is there any way I can achieve this?

Thank you.

1 ACCEPTED SOLUTION
johnt75
Super User
Super User
2 REPLIES 2
johnt75
Super User
Super User

this worked, thanks!

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.