topic Re: Switch Function not meeting conditions in DAX Commands and Tips

Switch Function not meeting conditions

Anonymous — Wed, 14 Jun 2023 16:08:17 GMT

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.

Re: Switch Function not meeting conditions

tamerj1 — Wed, 14 Jun 2023 17:07:07 GMT

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

Re: Switch Function not meeting conditions

Anonymous — Wed, 14 Jun 2023 17:35:56 GMT

The data type is Integer value

Re: Switch Function not meeting conditions

Anonymous — Wed, 14 Jun 2023 17:43:12 GMT

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

Re: Switch Function not meeting conditions

tamerj1 — Wed, 14 Jun 2023 17:46:04 GMT

Anonymous

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

Re: Switch Function not meeting conditions

Anonymous — Wed, 14 Jun 2023 17:53:13 GMT

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

also which 3 column view ?

Re: Switch Function not meeting conditions

tamerj1 — Wed, 14 Jun 2023 18:01:40 GMT

Anonymous

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

Re: Switch Function not meeting conditions

tamerj1 — Wed, 14 Jun 2023 18:05:12 GMT

Anonymous

These three columns

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

Re: Switch Function not meeting conditions

Anonymous — Wed, 14 Jun 2023 18:23:38 GMT

Re: Switch Function not meeting conditions

Anonymous — Wed, 14 Jun 2023 18:31:19 GMT

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

Re: Switch Function not meeting conditions

Anonymous — Wed, 14 Jun 2023 18:32:09 GMT

I attached images

Re: Switch Function not meeting conditions

tamerj1 — Wed, 14 Jun 2023 18:42:37 GMT

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.

Re: Switch Function not meeting conditions

tamerj1 — Thu, 15 Jun 2023 05:23:40 GMT

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

Re: Switch Function not meeting conditions

Anonymous — Thu, 15 Jun 2023 05:55:07 GMT

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

Re: Switch Function not meeting conditions

tamerj1 — Thu, 15 Jun 2023 06:29:17 GMT

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?