Forum Discussion
CASE statement to DAX with AND clause
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?
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.
4 Replies
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.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.
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.
