Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX Question with nested IF and OR functions

Hi,   I'm trying to create a custom column with a nested IF and OR function   I need the column to return "Car" if one of the two criteria are met: If ClaimData[Dx1] IN {"Z11.52", "Z20.822", "Z8...
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

     Hi, Anonymous 

    Try to create a calculate column below:

    Column =
VAR con1 =
    IF (
        'ClaimData'[Dx1]
            IN {
            "Z11.52",
            "Z20.822",
            "Z86.16",
            "M35.81",
            "M35.89",
            "J12.82",
            "U07.1",
            "U07.2"
        },
        "TRUE",
        "FALSE"
    )
VAR con2 =
    SWITCH (
        TRUE (),
        FIND ( "U10", 'ClaimData'[Dx1], 1 ), "TRUE",
        FIND ( "U08", 'ClaimData'[Dx1], 1 ), "TRUE",
        FIND ( "U09", 'ClaimData'[Dx1], 1 ), "TRUE",
        FIND ( "U12", 'ClaimData'[Dx1], 1 ), "TRUE",
        FIND ( "U11", 'ClaimData'[Dx1], 1 ), "TRUE",
        "FALSE"
    )
RETURN
    IF ( con1 = "TRUE" || con2 = "TRUE", "Car", "Not Car" )

    Note that the FIND function is case-sensitive and the SEARCH function is case-insensitive

     

    Is this the result you want? Hope this is useful to you

    Please feel free to let me know If you have further questions

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

jhartranft60's avatar
jhartranft60
Advocate IV
5 years ago

I'd recommend creating a dimension table that you can build a relationship to or, at the very least, do a LOOKUPVALUE on.  If for some reason that's not possible, you can simple do this:

Column = 
     VAR Variable1 = 
        IF(
            ClaimData[Dx1] = "Z11.52" ||
            ClaimData[Dx1] = "Z20.822" ||
            .
            .
            .
            ClaimData[Dx1] = "U07.2", 1,0)

     VAR Variable2 =
          IF( 

              SEARCH("U10",ClaimData[Dx1],1)>0 ||
              SEARCH("U08",ClaimData[Dx1],1)>0 ||
              .

              .

              .

              SEARCH("U11",ClaimData[Dx1],1)>0,1,0)

 

     RETURN IF(Variable1 =1 || Variable2=1,"Car","Not a Car")