cancel
Showing results for
Did you mean:
Helper I

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

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.

6 REPLIES 6
Super User

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 🙂

do not hesitate to give a kudo to useful posts and mark solutions as solution
Helper I

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

Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

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.

Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

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

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

Announcements

#### Launching new user group features

Learn how to create your own user groups today!