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
sarah8991
Frequent Visitor

If not these 5 values, then equal "Others"

Hi,

 

I have a column with multiple values, I'm trying to get anything that isn't equal to 5 specific values, to equal "Others". 

How can I do this?

 

Thanks!

2 ACCEPTED SOLUTIONS
vicky_
Super User
Super User

Create a calculated column:

Column = IF(
    Table[Column Name] in {"Specific Value 1", "Specific Value 1"...},
    Table[Column Name], //returns the same value in the row
    "Others"
)

View solution in original post

v-huijiey-msft
Community Support
Community Support

Hi @sarah8991 ,

 

Please follow these steps:

 

1. This is the original data I created:

vhuijieymsft_0-1708936149283.png

 

2. If "Name" is not equal to "A B C D E", it is equal to "Others", I created a calculated column:

vhuijieymsft_1-1708936149285.png

 

Category =
SWITCH(
     TRUE(),
     'Table'[Name] = "A", "CategoryA",
     'Table'[Name] = "B", "CategoryB",
     'Table'[Name] = "C", "CategoryC",
     'Table'[Name] = "D", "CategoryD",
     'Table'[Name] = "E", "CategoryE",
     "Others"
)

 

3. Drag the fields to the report page for display. The page effect is as shown below:

vhuijieymsft_0-1708936253337.png

 

If you have any further questions please feel free to contact me.

 

pbix file is attached.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

View solution in original post

3 REPLIES 3
v-huijiey-msft
Community Support
Community Support

Hi @sarah8991 ,

 

Please follow these steps:

 

1. This is the original data I created:

vhuijieymsft_0-1708936149283.png

 

2. If "Name" is not equal to "A B C D E", it is equal to "Others", I created a calculated column:

vhuijieymsft_1-1708936149285.png

 

Category =
SWITCH(
     TRUE(),
     'Table'[Name] = "A", "CategoryA",
     'Table'[Name] = "B", "CategoryB",
     'Table'[Name] = "C", "CategoryC",
     'Table'[Name] = "D", "CategoryD",
     'Table'[Name] = "E", "CategoryE",
     "Others"
)

 

3. Drag the fields to the report page for display. The page effect is as shown below:

vhuijieymsft_0-1708936253337.png

 

If you have any further questions please feel free to contact me.

 

pbix file is attached.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

lbendlin
Super User
Super User

Create a grouping with five groups, one for each of the items you want.  Then check the "Other" box to get the sixth group for free.

vicky_
Super User
Super User

Create a calculated column:

Column = IF(
    Table[Column Name] in {"Specific Value 1", "Specific Value 1"...},
    Table[Column Name], //returns the same value in the row
    "Others"
)

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.