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

[anands06]

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

[anands06]

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

[anands06]

Friends,

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

[Anonymous]

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

[anands06]

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

[Anonymous · Accepted solution]

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

[anands06]

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