CASE statement to DAX with AND clause

[Anonymous]

Hello!

Here is a CASE statement that I am looking to convert to DAX. My data model is built in Azure Analysis Services cube. In the model there are 2 tables Invoice Header and Invoice Items. One Inv Header records has many Inv Items records. 

CASE WHEN InvoiceHeader.InvoiceStatus == "C" THEN "CLOSED"
WHEN InvoiceHeader.InvoiceStatus == "O" THEN "OPEN"
WHEN InvoiceHeader.TransferToAccountingStatusDescription == "Posting Document Has Been created"
AND SUM(InvoiceItem.CashDiscountEligibleAmount) == 0 THEN "$0 Invoice"
ELSE Billing.InvoiceStatus END AS Status

Is it possible to have convert this CASE which has an 'AND' to DAX?

[MartynRamsden · Accepted solution]

Hi @Anonymous 

DAX has an AND function but it's a little limited as it only accepts 2 expressions.

As such, I prefer to use the double ampersand '&&' which is the AND operator in DAX.

Assuming you have the relavant relationship between your tables, you should be able to use this:

Status Column =
SWITCH (
    TRUE (),
    InvoiceHeader[InvoiceStatus] = "C", "CLOSED",
    InvoiceHeader[InvoiceStatus] = "O", "OPEN",
    InvoiceHeader[TransferToAccountingStatusDescription] = "Posting Document Has Been created"
        && CALCULATE (
            SUM ( InvoiceItem[CashDiscountEligibleAmount] )
        ) = 0, "$0 Invoice",
    InvoiceHeader[InvoiceStatus]
)

The else clause in your CASE statement references a table named 'Billing' but you didn't mention this in your description. My DAX expression will return InvoiceStatus from the InvoiceHeader table if none of the conditions are met.

Best regards,
Martyn

If I answered your question, please help others by accepting it as a solution. 

[Anonymous]

Thank you. Is it possible to 

SUM ( InvoiceItem[CashDiscountEligibleAmount] )

 at an Invpice Number level? 

[MartynRamsden]

Hi @Anonymous 

If you're creating a measure, a simple SUM should work as long as the Invoice Number is in the filter context.

If you're adding a calculated column to your InvoiceHeader table, you need to wrap the SUM in CALCULATE to force context transition e.g.

SumOfInvoiceLines = CALCULATE ( SUM ( InvoiceItem[CashDiscountEligibleAmount] ) )

Best regards,
Martyn

If I answered your question, please help others by accepting it as a solution.

[mwegener]

Hi @Anonymous ,

you can nest IF statements.

Look at this.

https://docs.microsoft.com/en-us/dax/if-function-dax

https://docs.microsoft.com/en-us/dax/and-function-dax

But data preparation should be done before loading the data into the analysis model.

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.