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
karkar
Helper III
Helper III

MULTIPLE IF CONDITIONS

Hello,

 

Can anyone help me with the DAX for this?

 

 

1 ACCEPTED SOLUTION

Hi @karkar,

In excel, we calculate based on cell, while in Power BI, we calculate the value based on column. The difference for DAX and excel function is: there are only logical conditions in AND in DAX. In excel, you can more than two conditions in AND like: AND(logical1, [logical2], ...). I create it in excel and get expected result.

In excel, AND(A8= "Not Hispanic or Latino Origin",B8<>"Unavailable",B8<>"Declined") is equals to AND(A8= "Not Hispanic or Latino Origin",AND(B8<>"Unavailable",B8<>"Declined"))

The results of using AND and OR in the red part are different. I confused it before, so I edit my last reply. Thanks for understanding. Now I test it in BI and Excel.  

=IF(OR(A8="Hispanic or Latino Origin Unknown",A8="Declined - patient is unwilling to respond"),"Unknown",IF(A8="Hispanic or Latino Origin", "Hispanic",IF(AND(A8= "Not Hispanic or Latino Origin",B8<>"Unavailable",B8<>"Declined"),B8,"Unknown")))

=IF(OR(A8="Hispanic or Latino Origin Unknown",A8="Declined - patient is unwilling to respond"),"Unknown",IF(A8="Hispanic or Latino Origin", "Hispanic",IF(AND(A8= "Not Hispanic or Latino Origin",OR(B8<>"Unavailable",B8<>"Declined")),B8,"Unknown")))



Using ANDUsing ANDUsing ORUsing OR
Best Regards,
Angelia

View solution in original post

10 REPLIES 10
bsas
Post Patron
Post Patron

Hi,

 

From your explanation I didn't understand what you want to receive in the end. Could you please provide more details.

 

Please try to use formula below:

 

Devired race_test = IF(OR(ETHNICITY = "Hispanic or Latino Origin Unknown","Declined - patient is unwilling to respond"), "Unknown",IF(ETHNICITY = "Hispanic or Latino Origin", "Hispanic",IF(AND(ETHNICITY = "Not Hispanic or Latino Origin", RACE <> "Unavailable"), RACE, "Unknown")))

Hello bsas,

 

Thansk for the help.This has gotten me very close to what i need.

I have added one more row  and how do i add Declined to the existing formulea????

@karkar

 

Devired race_test = IF(OR(ETHNICITY = "Hispanic or Latino Origin Unknown","Declined - patient is unwilling to respond"), "Unknown",IF(ETHNICITY = "Hispanic or Latino Origin", "Hispanic",IF(AND(ETHNICITY = "Not Hispanic or Latino Origin", OR(RACE <> "Unavailable", RACE <> "Declined")), RACE, "Unknown")))

Hi @karkar,

Please create a calculated column using the formula.

DERIVED RACE =
IF (
    OR (
        Table1[ETHNICITY] = "Hispanic or Latino Origin Unknown",
        Table1[ETHNICITY] = "Declined - patient is unwilling to respond"
    ),
    "Unknown",
    IF (
        Table1[ETHNICITY] = "Hispanic or Latino Origin",
        "Hispanic",
        IF (
            AND (
                Table1[ETHNICITY] = "Not Hispanic or Latino Origin",
                AND ( Table1[RACE] <> "Unavailable", Table1[RACE] <> "Declined" )
            ),
            Table1[RACE],
            "Unknown"
        )
    )
)


You will get expected result shown in the following screenshot.

1.PNG

Best Regards,
Angelia

Thansk v-Huizhn,

 

 

Hi @karkar,


As I tested,  when you use the OR in red part. The result both of the two rows("Unavailable" and "Declined" rows) is equals to result of any of the rows.

1.PNG

The result is different from using AND.

Best Regards,
Angelia

Thanks Angelia,

 

I tested first in Excel and the Unavialble and Declined are not being converted to UNKNOWN for "Not Hispanic or Latino Origin".

Do you think we have to do anything different in Excel?

 

Thank you

Hi @karkar,

In excel, we calculate based on cell, while in Power BI, we calculate the value based on column. The difference for DAX and excel function is: there are only logical conditions in AND in DAX. In excel, you can more than two conditions in AND like: AND(logical1, [logical2], ...). I create it in excel and get expected result.

In excel, AND(A8= "Not Hispanic or Latino Origin",B8<>"Unavailable",B8<>"Declined") is equals to AND(A8= "Not Hispanic or Latino Origin",AND(B8<>"Unavailable",B8<>"Declined"))

The results of using AND and OR in the red part are different. I confused it before, so I edit my last reply. Thanks for understanding. Now I test it in BI and Excel.  

=IF(OR(A8="Hispanic or Latino Origin Unknown",A8="Declined - patient is unwilling to respond"),"Unknown",IF(A8="Hispanic or Latino Origin", "Hispanic",IF(AND(A8= "Not Hispanic or Latino Origin",B8<>"Unavailable",B8<>"Declined"),B8,"Unknown")))

=IF(OR(A8="Hispanic or Latino Origin Unknown",A8="Declined - patient is unwilling to respond"),"Unknown",IF(A8="Hispanic or Latino Origin", "Hispanic",IF(AND(A8= "Not Hispanic or Latino Origin",OR(B8<>"Unavailable",B8<>"Declined")),B8,"Unknown")))



Using ANDUsing ANDUsing ORUsing OR
Best Regards,
Angelia

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.