Forum Discussion
Switch Column Logic Help
Hi All,
I'm in a pickle. I have the below logic using 3 columns on the same table:
14 Replies
Hi Anonymous
Since the condition on the number of employees supersedes all others, I would do something like the following, where [NumberOfEmployees] is whatever piece of code you use to know the number of employees under the leader in Greece, Italy, Turkey.
Label = IF ( [NumberOfEmployees] > 0, "Cars", SWITCH ( TRUE (), ActiveHC[Country Name] = "Turkey", "Cars", ActiveHC[Country Name] = "Greece", "Cars", ActiveHC[Country Name] = "Italy", "Cars", ActiveHC[Layer 2] = "Beth", "Corporate", ActiveHC[Layer 2] = "Joanie", "Corporate", ActiveHC[Layer 2] = "Dan", "Corporate", ActiveHC[Layer 2] = "Bill", "Corporate", ActiveHC[Layer 2] = "Christina", "Corporate", ActiveHC[Layer 2] = "Steven", "Cars", ActiveHC[Layer 2] = "Bobby", "Audio", ActiveHC[Layer 2] = "Matt", "Photos", ActiveHC[Layer 2] = "Peter", "Photos", ActiveHC[Layer 2] = "Edward", "Photos", ActiveHC[Layer 2] = "Joey", "Software", ActiveHC[Business Unit] = "Cameras", "Cameras", BLANK () ) )Hi AlB,
Thanks so much for this. However, I added additional context. Just can't seem to get this to work. :(
Can you provide some sample data and then what you want the results to look like? In text. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Hi Greg_Deckler & AlB
Thanks for the feedback. I have pasted sample data below:EE ID Country Division Layer 1 Layer 2 5464 Italy Cameras Paul Beth 5221 Greece Cars Paul Joanie 5845 Turkey Cars Paul Dan 231132 Turkey Corporate Paul Bill 112258 Greece Corporate Paul Christina 5843 USA Cars Paul Steven 123 USA Imaging Paul Jacob 46546 USA Photos Paul Matt 12132468 Sweden Audio Paul Peter 546544 Monaco Corporate Paul Edward 13132 France Software Paul Joey 12132 Italy Audio Paul Bobby
Regarding my original post, I want to create a calculated column called "Label". The layer 2 name should return the result of what I have outlined above in my original post. However, if the country is ITALY, GREECE or TURKEY, then I want it to be called "CARS" regardless of that person's name and desired value I outlined above. I essentially want the country to take over the label from the switch function and that if those 3 countries are NOT mentioned then it gives me the label that I have outlined above -- please note that Steven is also in charge of "Cars" though he is in the USA, which is fine.As you can see from the original post, Bobby would be audio, but since he has headcount in Italy, I would want Cars to be the label instead since he has headcount in Italy. The final table should look like this:
Label Count Cars 7 Imaging 1 Audio 1 Corporate 1 Software 1 Photos 1 I also want to use this as a field to put into a bigger data table for records export so that it looks like this:
EE ID Country Division Layer 1 Layer 2 Label 5464 Italy Cameras Paul Beth Cars 5221 Greece Cars Paul Joanie Cars 5845 Turkey Cars Paul Dan Cars 231132 Turkey Corporate Paul Bill Cars 112258 Greece Corporate Paul Christina Cars 5843 USA Cars Paul Steven Cars 123 USA Imaging Paul Jacob Imaging 46546 USA Photos Paul Matt Photos 12132468 Sweden Audio Paul Peter Audio 546544 Monaco Corporate Paul Edward Corporate 13132 France Software Paul Joey Software 12132 Italy Audio Paul Bobby Cars I hope this helps! Thanks so much all!!!!
Hi Greg_Deckler,
I was wondering if you had any additional insight on this now that I provided additional data. Leaving today on vacation through Monday. Been coming up dry :(
I tried doing some Var = logic, but no luck.
Many thanks as always.
