DAX Question with nested IF and OR functions

[Anonymous]

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", "Z86.16", "M35.81", "M35.89", "J12.82", "U07.1", "U07.2"

OR IF ClaimData[Dx1] contains  {"U10", "U08", "U09", "U12", "U11"}

Can you please assist?

[v-angzheng-msft · Accepted solution]

 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.

[selimovd]

Hey @Anonymous ,

there unfortunately no elegant way for the CONTAINS statement.

So I would do it like that:

Car Column =
IF(
    ClaimData[Dx1]
        IN {
            "Z11.52",
            "Z20.822",
            "Z86.16",
            "M35.81",
            "M35.89",
            "J12.82",
            "U07.1",
            "U07.2"
        }
        ||
            SEARCH("U10", ClaimData[Dx1]) + 
            SEARCH("U08", ClaimData[Dx1]) + 
            SEARCH("U09", ClaimData[Dx1]) + 
            SEARCH("U12", ClaimData[Dx1]) + 
            SEARCH("U11", ClaimData[Dx1]) > 0 ,
    1,
    0
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[jhartranft60]

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