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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rrhutch
Helper II
Helper II

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
konstantinos
Memorable Member
Memorable Member

@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

View solution in original post

3 REPLIES 3
konstantinos
Memorable Member
Memorable Member

@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

Thanks. That was helpful.

Dan80
Helper II
Helper II

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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