SWITCH Function for Duration values

[HelenBrunyee]

Hello Everyone,

I have had a good search on here to try and find someone with the same issue, but no luck, so was hoping someone would be able to help me 🙂

I am using the DAX function SWITCH(TRUE(), to label/categorise a list of durations. I have used SWITCH plenty of times for numeric or text values, but can't seem to figure out how I would use it for durational conditions.

I have a huge amount of durations(some days in length), but I am only really interested in the minutes, to get them labelled up/grouped into the following catgeories:

0-15 mins

16-30 mins

31-45 mins

46-60 mins

60+ mins

I am unsure how I would go about saying "IF >15 minutes & <31 minutes = "16-30 mins", fo example?

Any help would be super super appreciated!

Thank you,

Helen

[Jihwan_Kim]

Hi, @HelenBrunyee 

I am not sure if I understood correctly your question.

I suggest having a grouping table (separate table) like the below picture and write a measure to group the values that you want.

If it is OK with you, please share your sample pbix file's link here together with how you want to see your desired outcome. Then I can try to look into it to come up with a more accurate solution.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[HelenBrunyee]

Here's my reply

[HelenBrunyee]

Hi @Jihwan_Kim 

Thank you for getting back to me 🙂 Sorry if I haven't explained this well.

I am just trying to get something like the below, but using tduration conditions such as, "if greater than 15 minutes...". The below example is something I used to label locations based on postcodes:

London_Group = SWITCH(TRUE(),
LEFT(V_LOCATIONS[Post Code],2)="WD","Greater London",
LEFT(V_LOCATIONS[Post Code],2)="EN","Greater London",
LEFT(V_LOCATIONS[Post Code],2)="HA","Greater London",
LEFT(V_LOCATIONS[Post Code],2)="NW","Greater London",
LEFT(V_LOCATIONS[Post Code],2)="IG","Greater London",
LEFT(V_LOCATIONS[Post Code],2)="BR","Greater London",
LEFT(V_LOCATIONS[Post Code],2)="NE","Non-Greater London",
LEFT(V_LOCATIONS[Post Code],2)="WN","Non-Greater London",
LEFT(V_LOCATIONS[Post Code],2)="WF","Non-Greater London",
LEFT(V_LOCATIONS[Post Code],2)="WA","Non-Greater London",
"Non-Greater London") 

What I'm trying to do is write a SWITCH DAX like the above, to label my durations column.

I've tried something like this, but it won't work with the type as duration or with the colons:

Duration_Group = SWITCH(TRUE(),
[duration]<= 0.0:15:00, "0 - 15 Minutes",
[duration]> 0.0:15:00 & <=30, "16-30 Minutes",

Hope this helps explain it a bit better! 🙂

Thanks,

Helen

[Jihwan_Kim]

Hi, @HelenBrunyee 

Thank you for your feedback.

Can you share your sample pbix file?

I understand that you do not need extra grouping table, am I correct?

I think it is an issue with how to write DAX measure, but please kindly share your sample pbix file's link here, then I can try to come up with a more accurate measure.

Thanks.