Conditional formatting - IF else - for more than 500 different values

[Anonymous]

Hey!

Im having some issues with using the "Conditional Column" module, for creating groups from a column containing more than 500 different values.

Right now the code for this looks like " else if [Konto] = "20230" then "Eksterne omkostning" else if [Konto] = "20235" then "Eksterne omkostning" else if [Konto] = "20240"" etc. 

But I cant figure out how to make it into a shorter and easier to manage code. 

[az38]

Hi @Anonymous 

use switch() in DAX, in your case

Column = SWITCH([Konto],
"20230", "Eksterne omkostning", 
"20235", "Eksterne omkostning" ,
"Other")

 solutions for power query will not be very much beautiful 🙂

[Anonymous]

So if I want to have 10 different values in one group I could do it like this:

Column = Switch([Konto]),

"20230", "20030"; "Ekstern omkostning"; 

[v-lili6-msft]

hi  @Anonymous 

Yes, you could IF() or Switch() Function to get it, see more details by this blog:

https://powerpivotpro.com/2012/06/dax-making-the-case-for-switch/

Regards,

Lin

[Anonymous]

I used another method instead. By creating a SQL statement, it seems a little easier to create the new groups.

But I still need to figure out how to pick values between two values.

So from 1...5 = one group

6....8 = another group   

13..15 18..20 = a third group.

Hope it makes some sence.

[v-lili6-msft]

Hello @JessVinsteen

Try this logic:

Column?

SWITCH(TRUE(),

[Account]>1&&[Account]<-5, "a group",

[Account] > 6 & & [Account] < 8, "Other Group",

([Account]>-13&&[Account]<-15)-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ([Account]>-18&&[Account]<-20), "third group"

)

Best regards

Lin

[Greg_Deckler]

Create a second table in Excel or something with the translations between the numbers and the text values. Use a Merge query.