cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JessVinsteen
Helper I
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
az38
Super User
Super User

Hi @JessVinsteen 

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
LinkedIn

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

 

 

hi  @JessVinsteen 

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.

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.

 

 

 

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.
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.