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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anupal
Frequent Visitor

Need to Group field values to create custom column

Hello,

 

I am looking to creata a customer column by grouping values of that column and creating a new field. Below is what I am trying to get-

 

Current Values in the field Expected Values in the field
Country Country_CUSTOM
USA USA
JAPAN JAPAN
CHINA Others
INDIA 
UK 
   

 

I have a field called Country which has 5 values. I want to create a custom field with 2 values(USA and JAPAN) as is and club remaining 3 as OTHERS.

 

I would appreciate the suggestions on how to get this.

 

TIA

 

AP.

1 ACCEPTED SOLUTION

In that case your calculated column should look like this...

Country_CUSTOM_2 = 
SWITCH(
TRUE(),
[Country] IN {"USA", "JAPAN", "CHINA"}, [Country],
"OTHERS"
)

In this version you can add countries to the list as you wish and all other countries not listed will be "Others".




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

5 REPLIES 5
talespin
Solution Sage
Solution Sage

hi @Anupal 

 

There are many ways to group in power BI

Prefer Power Query

Otherwise, have you tried Power BI DM > Data Group?

 

talespin_1-1709135456848.png

 

 

jgeddes
Super User
Super User

A calculated column with the following should work.

Country_CUSTOM =
SWITCH(
TRUE(),
OR([Country] = "USA", [Country] = "Japan"), [Country],
"OTHERS"
)



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Hello @jgeddes , thanks for the solution,.

 

However I have a question, does the above logic works only on 2 values?

 

For Eg: If I have to show 3 countries (USA,JAPAN,CHINA) and rest 2 countries as OTHERS..then  also the same will work?

 

In that case your calculated column should look like this...

Country_CUSTOM_2 = 
SWITCH(
TRUE(),
[Country] IN {"USA", "JAPAN", "CHINA"}, [Country],
"OTHERS"
)

In this version you can add countries to the list as you wish and all other countries not listed will be "Others".




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Thanks @jgeddes , It worked. 🙂

 

Thanks Again.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors