Creating a category column to have an everything category and an other category

[KarenFingerhut]

Happy Friday

Hopefully someone can help me please

I'm trying to create a new column where I want a category to incorporate everything and then another category to only include a few. So for example 

Repairs 1

Repairs 2 

Repairs 3 

Repairs 4

Repairs 5 

Repairs 6 

In one category I want to include all 6 and in the other I only want to include 1,2,3. And then for instance give it a title of

Category Fix which includes the below

'All' Includes  1-6

Other Includes 1-3

Does that make sense

In my dataset there are many descriptions, the above is just for the purpose of explaining this. If there were only a few and they were static descriptions I could create a table myself and do it that way but if new ones were to appear they wouldn't automatically get included, so I can't do it that way

Hope someone can help please

Many thanks

Karen

[KarenFingerhut]

Good morning 

Sadly that didnt work. The actual split does works. So 

Repairs

All

However the calcualtions aren't working as they should.

Kind regards

Karen

[Anonymous]

Hi @KarenFingerhut ,

Thank you for your kind reply!

Could you please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

We could offer you more help if we have information in detail. Thank you for your understanding! Your time and cooperation are much valued by us. We are looking forward to hearing from you to assist further.

Best regards,

Lucy Chen

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

[bhanu_gautam]

@KarenFingerhut , Create a new column using

DAX
Category Fix =
SWITCH(
TRUE(),
'Table'[Description] IN {"Repairs 1", "Repairs 2", "Repairs 3"}, "Other",
'Table'[Description] IN {"Repairs 1", "Repairs 2", "Repairs 3", "Repairs 4", "Repairs 5", "Repairs 6"}, "All",
BLANK()
)

[KarenFingerhut]

Thanks @bhanu_gautam 

Ordinarily that would work. The problem is I have about 30 different descriptions and new ones could be added at any time, so I can't hardcode them all like you have done in your DAX statement. The only ones I can hardcode are the ones I want to split out as there is only 3

Many thanks

Karen

[bhanu_gautam]

@KarenFingerhut , Create a calculated column than 

IsOther =
IF(
'Table'[Description] IN {"Repairs 1", "Repairs 2", "Repairs 3"},
TRUE(),
FALSE()
)

And from that create one more

Category Fix =
IF(
'Table'[IsOther],
"Other",
"All"
)

[KarenFingerhut]

Thats great @bhanu_gautam i'll give that a go and let you know

Thanks Karen 😊

[Anonymous]

Hi @KarenFingerhut ,

Did the reply bhanu_gautam offered help you solve the problem, if it helps, you can consider to accept it as a solution so that more user can refer to, or if you have other problems, you can offer some information so that can provide more suggestion for you.

Thanks for your understanding. 

Best regards,

Lucy Chen

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