Need help with grouping a variable

[DomMether]

I have a variable called program. The data in program conforms to the format year-program where year can equal any year in the format yyyy, and program can be either 01, 04, 06, 09, 12. For example 2019-01, 2019-04, 2020-01, 2020-04, 2020-06.

I would like to create a new measure called 'program-grouped', where any program ending in '01' is called January, any program ending in '04' is called April, and any program ending in '06' is called June.

I tried the following but it did not work. Any ideas?

Program-Grouped =
SWITCH(
TRUE(),
RIGHT([program], 2) = "01", "January",
RIGHT([program], 2) = "04", "April",
RIGHT([program], 2) = "06", "June",
"Other"
)

[dharmendars007 · Accepted solution]

Hello @DomMether , 

Please try the below measure and also request you to share screenshot to check what error you are getting with the measure you are using..

Note - Ensure that the program field is a text field, as numeric data types might not work well with string functions like RIGHT() 

Program-Grouped =
SWITCH(
TRUE(),
RIGHT(TRIM([program]), 2) = "01", "January",
RIGHT(TRIM([program]), 2) = "04", "April",
RIGHT(TRIM([program]), 2) = "06", "June","Other")

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[Anonymous]

Hi, @DomMether 

Is that what you were expecting?

If you are trying to batch convert month number to month name, the method provided by @lbendlin  is much easier to follow.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dharmendars007 · Accepted solution]

Hello @DomMether , 

Please try the below measure and also request you to share screenshot to check what error you are getting with the measure you are using..

Note - Ensure that the program field is a text field, as numeric data types might not work well with string functions like RIGHT() 

Program-Grouped =
SWITCH(
TRUE(),
RIGHT(TRIM([program]), 2) = "01", "January",
RIGHT(TRIM([program]), 2) = "04", "April",
RIGHT(TRIM([program]), 2) = "06", "June","Other")

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[DomMether]

I can't tell because for some reason I can't reference the column in my DAX formula.  Got any idea why that is?

[Anonymous · Accepted solution]

Hi, @DomMether 

You can try this formula.

Measure = 
SWITCH(
TRUE(),
RIGHT(TRIM(SELECTEDVALUE('Table'[Program])), 2) = "01", "January",
RIGHT(TRIM(SELECTEDVALUE('Table'[Program])), 2) = "04", "April",
RIGHT(TRIM(SELECTEDVALUE('Table'[Program])), 2) = "06", "June",
"Other"
)

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

=

FORMAT(datevalue([program] & "-01"),"mmmm")