Custom text output based on number values

[KGBOR]

Hello,

I'm having a rather simple issue but I can't figure it out how to do it in DAX.

I have the lenght of service of employees as a number in years (1 = 12 months, 0.5 = 6 months, 0.25 = 3 months, etc) and as an output I need to create categories for certain intervals as a text. Like people with less than 0.25 years should give me "0-3 months" as a result, between 0.25 and 1 would result in "3-12 months" and so on. What would be the best way to approach it?

Thank you!

[MAwwad · Accepted solution]

You can use the SWITCH function in DAX to create the categories based on the length of service of employees. Here's an example of how to do it:

Service Category = SWITCH( TRUE(), [Length of Service] < 0.25, "0-3 months", [Length of Service] < 1, "3-12 months", [Length of Service] < 2, "1-2 years", [Length of Service] < 5, "2-5 years", [Length of Service] >= 5, "5+ years" )

In the above example, replace [Length of Service] with the name of the column that contains the length of service of employees in years. The SWITCH function evaluates each condition in the order they are listed and returns the corresponding category text if the condition is true. If none of the conditions are true, it returns blank. You can adjust the conditions and category texts to fit your specific intervals.

[KGBOR]

Thank you very much for the detailed answer! Just for my understanding: what does the TRUE operator do in this formula? The rest I understand now but that one is not entirely clear right now. Thank you again!

[ryan_mayu]

@KGBOR 

you can try this

category = SWITCH(TRUE(),'Table'[length of service]<0.25,"0-3 mths",'Table'[length of service]<1,"3-12 mths",">12mths")

[KGBOR]

Thank you very much!