cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
vaidehi27 Frequent Visitor
Frequent Visitor

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

Accepted Solutions
Super User I
Super User I

Re: CASE statement to DAX with AND clause

Hi @vaidehi27 

 

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. 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Super User III
Super User III

Re: CASE statement to DAX with AND clause

Hi @vaidehi27 ,

 

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.

Proud to be a Super User!

Super User I
Super User I

Re: CASE statement to DAX with AND clause

Hi @vaidehi27 

 

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. 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

vaidehi27 Frequent Visitor
Frequent Visitor

Re: CASE statement to DAX with AND clause

Thank you. Is it possible to 

SUM ( InvoiceItem[CashDiscountEligibleAmount] )

 at an Invpice Number level? 

Super User I
Super User I

Re: CASE statement to DAX with AND clause

Hi @vaidehi27 

 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors