Tableau flag code translation?

[k3rz0rg]

Hi everyone, 

I created a flag on tableau which works fine but now I have to translate that in DAX and I tried with if-else and it's throwing me error.

Could you please help me with it?

Tableau code:

If [Code] = 'D' 
OR

( [Code] = 'C' and [ID] <> '' and NOT ISNULL([ID]) )
THEN 1 ELSE 0 END

I tried the following code but didn't work and in DAX, AND or OR operators take only 2 parameters and my second part of OR has 3. 

IF(

    OR( VALUES('NA Pharmacy Distribution'[Sale_Code]) = "D" ,

        AND( VALUES('NA Pharmacy Distribution'[Sale_Code]) = "C", VALUES('NA Pharmacy Distribution'[Contract_ID]) <> "", NOT(ISBLANK(VALUES('NA Pharmacy Distribution'[Contract_ID]))))

    )

    ,1

    ,0

)

Any help would be greatly appreciated.

Thanks! 

[OwenAuger · Accepted solution]

Hi @k3rz0rg 

It looks like you're creating a calculated column - is that right?

I would suggest this. I've used variables for readability and you can use the && operator instead of the AND function to handle more than 2 conditions.

Also, VALUES isn't needed if you are referring to a column's value in a row context (assuming this is a calculated column). Just use the column reference directly.

Flag = 
VAR SaleCode = 'NA Pharmacy Distribution'[Sale_Code]
VAR ContractID = 'NA Pharmacy Distribution'[Contract_ID]
RETURN
    IF (
        OR (
            SaleCode = "D",
            SaleCode = "C" && ContractID <> "" && NOT ( ISBLANK ( ContractID ) )
        ),
        1,
        0
    )

Regards

[v-yilong-msft]

Hi @k3rz0rg ,

I’d like to acknowledge the valuable input provided by the @OwenAuger . His initial ideas were instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue.  

In my investigation, I took the following steps:

I create a table as you mentioned.

Then I change your DAX code and get what you want.

Column =
IF (
    'NA Pharmacy Distribution'[Sale_Code] = "D"
        || (
            'NA Pharmacy Distribution'[Sale_Code] = "C"
                && NOT ISBLANK ( 'NA Pharmacy Distribution'[Contract_ID] )
                    && 'NA Pharmacy Distribution'[Contract_ID] <> ""
        ),
    1,
    0
)

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[OwenAuger · Accepted solution]

Hi @k3rz0rg 

It looks like you're creating a calculated column - is that right?

I would suggest this. I've used variables for readability and you can use the && operator instead of the AND function to handle more than 2 conditions.

Also, VALUES isn't needed if you are referring to a column's value in a row context (assuming this is a calculated column). Just use the column reference directly.

Flag = 
VAR SaleCode = 'NA Pharmacy Distribution'[Sale_Code]
VAR ContractID = 'NA Pharmacy Distribution'[Contract_ID]
RETURN
    IF (
        OR (
            SaleCode = "D",
            SaleCode = "C" && ContractID <> "" && NOT ( ISBLANK ( ContractID ) )
        ),
        1,
        0
    )

Regards

[k3rz0rg · Accepted solution]

Hi @OwenAuger , thank you for your response! I was trying to create a calculated measure and almost forgot that I could create a column as well since I'm the owner of the dataset. Your suggestion helped me to solved my purpose!