topic Re: Need help with grouping a variable in DAX Commands and Tips

Need help with grouping a variable

DomMether — Mon, 23 Sep 2024 04:30:31 GMT

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"
)

Re: Need help with grouping a variable

lbendlin — Mon, 23 Sep 2024 21:26:50 GMT

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

Re: Need help with grouping a variable

dharmendars007 — Tue, 24 Sep 2024 01:59:37 GMT

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

Re: Need help with grouping a variable

Anonymous — Mon, 07 Oct 2024 07:29:26 GMT

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.

Re: Need help with grouping a variable

DomMether — Thu, 26 Sep 2024 05:12:20 GMT

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

Re: Need help with grouping a variable

Anonymous — Thu, 26 Sep 2024 05:45:43 GMT

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.