Switch Function not meeting conditions

[Anonymous]

Hello I have been trying to create a new column with the location name of the org. I was abel to write this code:

CTL Location =

SWITCH(

TRUE(),

'IP_Current_Staff'[chCurrGroupCode] = "123" && 'IP_Current_Staff'[chCurrOfficeCode] = "NAA" && 'IP_Current_Staff'[chCityCode] ="YXU", "Canada AC",

'IP_Current_Staff'[chCurrGroupCode] = "456" && 'IP_Current_Staff'[chCurrOfficeCode] = "NAA" && 'IP_Current_Staff'[chCityCode] ="YUL", "Canada AC",

'IP_Current_Staff'[chCurrGroupCode] = "789" && 'IP_Current_Staff'[chCurrOfficeCode] = "NAA" && 'IP_Current_Staff'[chCityCode] ="YYZ", "Canada AC",

'IP_Current_Staff'[chCurrGroupCode] = "234" && 'IP_Current_Staff'[chCurrOfficeCode] = "NTX" && 'IP_Current_Staff'[chCityCode] ="YKF", "Canada AC", "Local Offcice")

Even though i see in the table that conditions are met it still returns the Local Office as the name in some rows.

[tamerj1]

@Anonymous 
Manually insert the 'Locations' table as follows

Group code	Office Code	City Code	CTL Location Name
101	IN1	IAC	Kolkata AC
103	NAA	YXU	Canada AC
141	IN1	IAC	Kolkata AC
141	IN1	TPL	TPL
143	NAA	YUL	Canada AC
197	NAA	YYZ	Canada AC
217	IN1	BAC	Bangalore AC
224	IN1	BAC	Bngalore AC
224	IN1	PSD	Poland AC
224	IN1	SAC	Shanghai AC
225	IN1	IAC	Kolkata AC
225	IN1	BAC	Bangalore AC
225	IN1	SAC	Shanghai AC
230	IN1	BAC	Bangalore AC
230	IN1	IAC	Kolkata AC
231	IN1	BAC	Bangalore AC
233	NAC	OAK	Canada AC
233	NAC	YYC	Canada AC
233	NAC	YXU	Canada AC
233	NAC	TPL	Canada AC
233	NAC	YKF	Canada AC
233	NAC	YKZ	Canada AC
233	NAC	YNJ	Canada AC
233	NAC	YOW	Canada AC
233	NAC	YQG	Canada AC
233	NAC	YUL	Canada AC
233	NAC	YVR	Canada AC
233	NAC	YYZ	Canada AC
233	NAC	YWG	Canada AC
240	IN1	IAC	Kolkata AC
242	IN1	BAC	Bangalore AC
243	IN1	IAC	Kolkata AC
243	IN1	BAC	Bangalore AC
244	IN1	IAC	Kolkata AC
244	IN1	BAC	Bangalore AC
244	IN1	SAC	Shanghai AC
245	IN1	BAC	Kolkata AC
245	IN1	IAC	Bangalore AC
246	IN1	IAC	Kolkata AC
255	IN1	IAC	Kolkata
255	IN1	TPL	TPL
612	IN1	IAC	Kolkata
612	IN1	TPL	TPL
817	NAC	OAK	Canada AC
817	NAC	YYC	Canada AC
817	NAC	YXU	Canada AC
817	NAC	TPL	Canada AC
817	NAC	YKF	Canada AC
817	NAC	YKZ	Canada AC
817	NAC	YNJ	Canada AC
817	NAC	YOW	Canada AC
817	NAC	YQG	Canada AC
817	NAC	YUL	Canada AC
817	NAC	YVR	Canada AC
817	NAC	YYZ	Canada AC
817	NAC	YWG	Canada AC
835	NTX	YKF	Canada AC
835	NTX	YXU	Canada AC
835	NTX	YYZ	Canada AC

The calculated column would be

CTL Location =
MAXX (
    FILTER (
        Locations,
        Locations[Group Code] = 'IP_Current_Staff'[chCurrGroupCode]
            && Locations[City Code] = 'IP_Current_Staff'[chCityCode]
            && Locations[Office Code] = 'IP_Current_Staff'[chCurrOfficeCode]
    ),
    Locations[CTL Location Name]
)

[Anonymous]

I wont be able to manually enter this as it is directly querried from a SQL database which i dont have access to. Plus I want to use it for a slicer dropdown

[tamerj1]

@Anonymous 
You can be at direct query mode and yet you can have a direct import table. However, you may also ask the data owner to include this table with the data set. Otherwise it would be a DAX code of multiple pages! I don't think you want that, do you?
What slicer do you want to use?

[tamerj1]

@Anonymous 

These three columns

'IP_Current_Staff'[chCurrGroupCode], 'IP_Current_Staff'[chCurrOfficeCode] and 'IP_Current_Staff'[chCityCode]

[Anonymous]

I attached images

[tamerj1]

@Anonymous 

That requires working on the laptop. I'll work out something tomorrow morning. Can you provide editable version of the same? You can just copy/paste the values in a reply. 

[Anonymous]

there is going to be multiple locations, so how should I code for different locations ?

[tamerj1]

@Anonymous 

Do you have a list of each location and it's three column values?

[Anonymous]

The locations are :
Bangalore AC, Kolkata AC, Canada AC, Shanghai AC, Poland AC.

also which 3 column view ? 

[tamerj1]

@Anonymous 

what are the associated values of the three columns for each of these locations?

[Anonymous]

[Anonymous]

The data type is Integer value

[tamerj1]

Hi @Anonymous 

What is the data type of the 'IP_Current_Staff'[chCurrGroupCode] column?

However, this should work regardless of the data type 

CTL Location =
SWITCH (
TRUE (),
'IP_Current_Staff'[chCurrGroupCode] & 'IP_Current_Staff'[chCurrOfficeCode] & 'IP_Current_Staff'[chCityCode]
IN { "123NAAYXU", "456NAAYUL", "789NAAYYZ", "234NTXYKF" }, "Canada AC",
"Local Offcice"
)

[Anonymous]

When I run this it shows CONTAINSROW in directquery is not allowed