Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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?

1 ACCEPTED SOLUTION
MartynRamsden
Solution Sage
Solution Sage

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. 

View solution in original post

4 REPLIES 4
MartynRamsden
Solution Sage
Solution Sage

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
Not applicable

Thank you. Is it possible to 

SUM ( InvoiceItem[CashDiscountEligibleAmount] )

 at an Invpice Number level? 

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.