Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
anands06
Frequent Visitor

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

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:

anands06_0-1691065432340.png

 

 

Best Regards

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

6 REPLIES 6
anands06
Frequent Visitor

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
Frequent Visitor

Friends,

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

Anonymous
Not applicable

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

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
Not applicable

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

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.