Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
DomMether
Regular Visitor

Need help with grouping a variable

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

2 ACCEPTED SOLUTIONS
dharmendars007
Solution Sage
Solution Sage

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 

View solution in original post

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

vzhangtinmsft_0-1727329527253.png

 

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.

 

View solution in original post

5 REPLIES 5
v-zhangtin-msft
Community Support
Community Support

Hi, @DomMether 

 

Is that what you were expecting?

vzhangtinmsft_0-1727156694595.png

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
Solution Sage
Solution Sage

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 

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

DomMether_0-1727327530868.png

 

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

vzhangtinmsft_0-1727329527253.png

 

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
Super User
Super User

=

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors