topic Re: Switch(TRUE() and OR function to validate and count entries in columns in DAX Commands and Tips

Switch(TRUE() and OR function to validate and count entries in columns

anands06 — Thu, 03 Aug 2023 12:24:52 GMT

Hello Friends,

Need your advise if I can use SWITCH(TRUE() and || function like below:

In power pivot I have 2 columns named as
[X3<=AA], [AF>(AA+2)]. Then column [X3<=AA] has following values "NoAACarr","NoX3Carr", "DET=Y" and column [AF>(AA+2)] has following values "NoAFCarr", "DET=Y"

I want to first check if all the three columns has one these values in their field "NoAACarr","NoX3Carr","NoAFCarr", I apply OR (||) function if not then filter the 2 columns where it has "DET=Y".

is the below correct DAX formula

DET(X3_OnTime):=SWITCH(TRUE(),

[X3<=AA]="NoAACarr" || [X3<=AA]="NoX3Carr" || [AF>(AA+2)]="NoAFCarr" ,"NoEvent",
[X3<=AA] = "DET=Y" && [AF>(AA+2)]="DET=Y","DET(X3_OnTime)=Y","DET=N")

Once i work up the formula data looks like this:

Best Regards

Re: Switch(TRUE() and OR function to validate and count entries in columns

anands06 — Thu, 03 Aug 2023 18:18:01 GMT

Friends,

I worked and validated the data and above formula works well.

Re: Switch(TRUE() and OR function to validate and count entries in columns

Anonymous — Mon, 07 Aug 2023 08:34:22 GMT

Hi anands06,

I checked you formula and condition and find they can be simplify to following expressions, you can try it if hleps:

DET(X3_OnTime) = IF ( [X3<=AA] <> "DET=Y" || [AF>(AA+2)] <> "DET=Y", "NoEvent", "DET(X3_OnTime)=Y" )

Comment:

If statement to get reverse of the first condition to check if these two column not equal to "DET=Y" with OR logic. If they not match sceniaros means both two column equal to "DET=Y" and they match wiht your second condition.

Regards,

Xiaoxin Sheng

Re: Switch(TRUE() and OR function to validate and count entries in columns

anands06 — Tue, 08 Aug 2023 06:25:31 GMT

Hello Xiaoxin Sheng,

Thanks for taking out the time and giving valuable advise.

It works well however I also wanted to add another condition of "DET=N"

As per your formula we may miss "DET=N".

regards

Anand

Re: Switch(TRUE() and OR function to validate and count entries in columns

anands06 — Wed, 09 Aug 2023 07:11:17 GMT

Hello Xiaoxin Sheng,

Thanks for taking out the time and giving valuable advise.

It works well however I also wanted to add another condition of "DET=N"

As per your formula we may miss "DET=N". I hope you agree with my response.

regards

Anand

Re: Switch(TRUE() and OR function to validate and count entries in columns

Anonymous — Fri, 11 Aug 2023 08:43:04 GMT

Hi anands06,

You can try to use this one, I add condition to recogined the "DEF=N" flag:

DET(X3_OnTime) = IF ( [X3<=AA] <> "DET=Y" || [AF>(AA+2)] <> "DET=Y", "NoEvent", IF ( [X3<=AA] = [AF>(AA+2)], "DET(X3_OnTime)=Y", "DET=N" ) )

Comment:

First condition check 'No Events' flag, if not match it mean at least one field equal to "DEF=Y".
The second condition check if two fields have same value, since the default condition is at least one field equal to "DEF=Y", so it will trigger if two columns equal to "DEF=Y". If both previous two conditions all not matched, the formula will return "DEF=N".
Regards,

Xiaoxin Sheng

Re: Switch(TRUE() and OR function to validate and count entries in columns

anands06 — Fri, 11 Aug 2023 16:39:59 GMT

Hello Xiaoxin,

Thanks for your attention. It works perfectly fine.

Both IF and my SWITCH(TRUE() formula show the same result.

Good interaction with you.

best regards

Anand