topic Re: SWITCH Function for Duration values in DAX Commands and Tips

SWITCH Function for Duration values

HelenBrunyee — Fri, 07 May 2021 07:17:23 GMT

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

Re: SWITCH Function for Duration values

Jihwan_Kim — Fri, 07 May 2021 07:37:13 GMT

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

Re: SWITCH Function for Duration values

HelenBrunyee — Fri, 07 May 2021 08:59:34 GMT

Here's my reply

Re: SWITCH Function for Duration values

HelenBrunyee — Fri, 07 May 2021 09:06:14 GMT

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

Re: SWITCH Function for Duration values

Jihwan_Kim — Fri, 07 May 2021 10:37:07 GMT

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.