cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rrhutch Regular Visitor
Regular Visitor

Getting my head around IFs and ORs in DAX

I am transitioning from Tablau to PowerBI, and having an issue writing a new Column statement in DAX. My statement in Tableau works similar to Excel ands reads as follows:

 

IF ([App Status]="AC" OR [App Status]="AR" OR [App Status]="AM" OR [App Status]="ACOM") THEN "Y" ELSEIF ([App Status] = "Can" OR [App Status] = "Def" OR [App Status] = "NoCon" OR [App Status] = "W") AND [App Status Date]<=[SumDate] THEN "N" ELSE "Y" END

 

I tried the syntax including the OR statement on the outside, but it gave an error. Also, wasn't clear on the ElseIf capabilities.

 

Any help would be greatly appreciated.

 

1 ACCEPTED SOLUTION

Accepted Solutions
konstantinos Senior Member
Senior Member

Re: Getting my head around IFs and ORs in DAX

@rrhutch  Like @Dan80 mentioned DAX syntax is different  OR is || and AND is && for more than two conditions, plus you need to add semicolon or comma depends region delimiter settings like excel.

 

IFDAX =
IF (
    [App Status] = "AC"
        || [App Status] = "AR"
        || [App Status] = "AM"
        || [App Status] = "ACOM";
    "Y";
    IF (
        [App Status] = "Can"
            || [App Status] = "Def"
            || [App Status] = "NoCon"
            || [App Status] = "W"
            && [App Status Date] <= [SumDate];
        "N";
        "Y"
    )
)

Or a better sformula for nested IF is SWITCH 

 

SWITCHDAX =
SWITCH (
    TRUE;
    [App Status] = "AC"
        || [App Status] = "AR"
        || [App Status] = "AM"
        || [App Status] = "ACOM"; "Y";
    [App Status] = "Can"
        || [App Status] = "Def"
        || [App Status] = "NoCon"
        || [App Status] = "W"
        && [App Status Date] <= [SumDate]; "N";
    "Y"
)

 

I suggest you to check DAX formula language definitions , at least on begginning

Konstantinos Ioannou
3 REPLIES 3
Highlighted
Dan80 Regular Visitor
Regular Visitor

Re: Getting my head around IFs and ORs in DAX

OR and AND functions in DAX have a limit of just 2 conditions so to get around the problem, instead of using OR, use | character and AND use &&. These allow you to use more than 2 conditions. Hope this helps?

konstantinos Senior Member
Senior Member

Re: Getting my head around IFs and ORs in DAX

@rrhutch  Like @Dan80 mentioned DAX syntax is different  OR is || and AND is && for more than two conditions, plus you need to add semicolon or comma depends region delimiter settings like excel.

 

IFDAX =
IF (
    [App Status] = "AC"
        || [App Status] = "AR"
        || [App Status] = "AM"
        || [App Status] = "ACOM";
    "Y";
    IF (
        [App Status] = "Can"
            || [App Status] = "Def"
            || [App Status] = "NoCon"
            || [App Status] = "W"
            && [App Status Date] <= [SumDate];
        "N";
        "Y"
    )
)

Or a better sformula for nested IF is SWITCH 

 

SWITCHDAX =
SWITCH (
    TRUE;
    [App Status] = "AC"
        || [App Status] = "AR"
        || [App Status] = "AM"
        || [App Status] = "ACOM"; "Y";
    [App Status] = "Can"
        || [App Status] = "Def"
        || [App Status] = "NoCon"
        || [App Status] = "W"
        && [App Status Date] <= [SumDate]; "N";
    "Y"
)

 

I suggest you to check DAX formula language definitions , at least on begginning

Konstantinos Ioannou
rrhutch Regular Visitor
Regular Visitor

Re: Getting my head around IFs and ORs in DAX

Thanks. That was helpful.

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 40 members 911 guests
Please welcome our newest community members: