Forum Discussion

karkar's avatar
karkar
Helper III
8 years ago
Solved

MULTIPLE IF CONDITIONS

Hello,   Can anyone help me with the DAX for this?    
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    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 OR
    Best Regards,
    Angelia

bsas's avatar
bsas
Post Patron
8 years ago

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")))

karkar's avatar
karkar
Helper III
8 years ago

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????

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    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.



    Best Regards,
    Angelia

    • karkar's avatar
      karkar
      Helper III
      8 years ago

      Thansk v-Huizhn,

       

       

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        8 years ago

        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.



        The result is different from using AND.

        Best Regards,
        Angelia

  • bsas's avatar
    bsas
    Post Patron
    8 years ago

    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")))