Switch Column Logic Help

[Anonymous]

Hi All,

I'm in a pickle. I have the below logic using 3 columns on the same table:

Label = 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()

)

All of these people listed above have employees under them in Turkey, Greece, and Italy. What I need to happen is that if ANY of of these leaders have employees in those countries, I need the label "Cars" to supercede the additional labels I've given. Does that make sense? So for example, if Edward has 5 employees that are in those countries, I want them to be labeled Cars instead of Photos because the country needs to trump the alternate label I'm giving. I basically need everything to go into 1 column. 

I tried using some VAR logic, but just wasn't getting the right result. I'm probably over complicating it!  Any help would be great please.

[Greg_Deckler]

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

[Anonymous]

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!!!!

[Anonymous]

@Anonymous - what is the incorrect result that you're getting?

[Anonymous]

@Anonymous 

I'm trying to write logic like this to get the above return, but not having luck:

Label =
VAR _Country = SWITCH(
TRUE (),
ActiveHC[Country Name] = "Turkey", "Cars",
ActiveHC[Country Name] = "Greece", "Cars",
ActiveHC[Country Name] = "Italy", "Cars",
Blank()
)
VAR _Segment = SWITCH(
True(),
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 ()
)
)
RETURN IF _COUNTRY  

Essentially, If Country is equal to one of those 3 countries I want it to be called "CARS" -- I want that to default regardless of the additional criteria I have below. However, if the country does NOT equal those 3, I want it to return the other values I have broken out. 

I just can't get the RETURN IF COUNTRY thing right using the 2 VAR pieces. 

[Anonymous]

@Anonymous - Does this work?:

Label =
SWITCH(
    TRUE(),
    ActiveHC[Country Name] IN { "Turkey", "Greece", "Italy" }, "Cars",
    ActiveHC[Layer 2] IN { "Beth", "Joanie", "Dan", "Bill", "Christina" }, "Corporate",
    ActiveHC[Layer 2] = "Steven", "Cars",
    ActiveHC[Layer 2] = "Bobby", "Audio",
    ActiveHC[Layer 2] = IN { "Matt", "Peter", "Edward" }, "Photos",
    ActiveHC[Layer 2] = "Joey", "Software",
    ActiveHC[Business Unit] = "Cameras", "Cameras",
    BLANK()
)

[Anonymous]

Thanks for this, but it will not work. That's a standard SWITCH which I can do, I'm trying to make it so the COUNTRY is the default return if it is NOT one of those countries, then return the other switch function.

[Anonymous]

@Anonymous -

Switch will look for the first true statement and return the associated value. In this example, the first check is the country, so if that country is found, it will return Cars. 

This won't work if you the label is dependent on rows other than the current row. If you want to check conditions in other rows, we'll need to understand the requirement more precisely.

Hope this helps,

Nathan

[Anonymous]

@Anonymous Hi Nate, 

All my requirements are stated in the post. @Greg_Deckler and @AlB  suggested I add more context which I did. I believe everything is there that is needed. I don't know how else to explain it. 

The short end of the stick is, If the country equals those 3 countries then I need it to default to CARS, otherwise, I need the other labels represented. The problem is that some of those managers also have people in those countries, but I want the CARS label to be overwritten. That's why I included a BLANK value piece in my switch to help it along. 

[Anonymous]

@Anonymous -

Again, I'm not sure I understand the requirement clearly, but @AlB has a good answer. I will expand on the answer a bit by defining what I think is your EmpCount:

Label =
var EmpCount = COUNTROWS(FILTER(ALL('ActiveHC'), ActiveHC[Layer 2] = EARLIER(ActiveHC[Layer 2]) && 'ActiveHC'[Country Name] in {"Turkey","Greece","Italy"}))
IF (
    EmpCount > 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 ()
    )
)

[Anonymous]

Thanks @Anonymous, it's stating that the syntax for IF is incorrect. 

[Anonymous]

@Anonymous -

Sorry, I forgot the RETURN:

Label =
var EmpCount = COUNTROWS(FILTER(ALL('ActiveHC'), ActiveHC[Layer 2] = EARLIER(ActiveHC[Layer 2]) && 'ActiveHC'[Country Name] in {"Turkey","Greece","Italy"}))

RETURN IF (...

[Anonymous]

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.

[AlB]

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 ()
    )
)

[Anonymous]

Hi @AlB,

Thanks so much for this. However, I added additional context. Just can't seem to get this to work. 😞