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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
misunika95
Frequent Visitor

DAX statement with Ifs and nested SUMIFS

Hi!

 

So I have a table with acccounts receivable info per account that has the following columns:

 

  • group: Credit/Cash
  • accountNumber
  • debtUSD
  • invoiceType
  • collectionsAgent
  • timeInterval

I'm trying to clasify their type fo debt based on several conditions. I had previously calculated this in the same Excel table with this formula:

 

=IF(IF([@group]<>"CASH",SUMIF([accountNumber],[@accountNumber],[debtUSD]))<0,"Credit Balance",IF(AND([@[debtUSD]]<0,OR([@[invoiceType]]="AA",[@[invoiceType]]="BB",[@[invoiceType]]="CC",[@[invoiceType]]="DD",[@[invoiceType]]="EE",[@[invoiceType]]="WV")),"Unapplied Payment",IF(OR([@[collectionsAgent]]="JohnDoe"),"Legal",[@[timeInterval]])))

 

In summary, the formula will calculate and classify as "Credit Balance", "Unapplied Payment" or "Legal" depending on the group, the amount of debt and the invoice type. Else, it will return the aging of the debt previously calculated on the column timeInterval (if it's 1-30 days old, 31-60, etc). 

 

I was wondering if there is an equivalent expression in DAX I can use. I would really appreciate the help guys 🙂

1 ACCEPTED SOLUTION
Anonymous
Not applicable

OK.

 

Classification = 
var __currentAccountNumber = 'Accounts Receivable'[Account Number]
var __currentDebt = 'Accounts Receivable'[Debt USD]
var __currentInvoiceType = 'Accounts Receivable'[Invoice Type]
var __currentTimeInterval = 'Accounts Receivable'[Time Interval]
var __sumOfDebtForCurrentClient = 
    SUMX(
        FILTER(
            'Accounts Receivable',
            'Accounts Receivable'[Account Number] = __currentAccountNumber
        ),
        'Accounts Receivable'[Debt USD]
    )
var __classification =
    SWITCH( TRUE(),

        __sumOfDebtForCurrentClient < 0, 
            "Credit Balance",
            
        __currentDebt < 0 && __currentInvoiceType in {"AA", "BB", "CC", "DD"},
            "Unapplied Payment",

        // else
        __currentTimeInterval
    )
return
    __classification

This is your formula for the Classification Column in your table.

 

If this is not performant enough, then here's a variation on this topic. Just change these lines:

 

var __sumOfDebtForCurrentClient = 
    SUMX(
        FILTER(
            'Accounts Receivable',
            'Accounts Receivable'[Account Number] = __currentAccountNumber
        ),
        'Accounts Receivable'[Debt USD]
    )

to these

 

var __sumOfDebtForCurrentClient =
    CALCULATE(
        SUM( 'Accounts Receivable'[Debt USD] ),
        ALLEXCEPT( 
            'Accounts Receivable',
            'Accounts Receivable'[Account Number] 
        )
    )

Not sure which one would be faster on a big table.

 

By the way, here's the Excel formula (which seems to implement a different logic than the one you've described), courtesy of http://excelformulabeautifier.com/:

=IF(
    IF(
        [@group] <> "CASH",
        SUMIF(
            [accountNumber],
            [@accountNumber],
            [debtUSD]
        )
    ) < 0,
    "Credit Balance",
    IF(
        AND(
            [@[debtUSD]] < 0,
            OR(
                [@[invoiceType]] = "AA",
                [@[invoiceType]] = "BB",
                [@[invoiceType]] = "CC",
                [@[invoiceType]] = "DD",
                [@[invoiceType]] = "EE",
                [@[invoiceType]] = "WV"
            )
        ),
        "Unapplied Payment",
        IF(
            OR(
                [@[collectionsAgent]] = "JohnDoe"
            ),
            "Legal",
            [@[timeInterval]]
        )
    )
)

But I've implemented the logic you've described 🙂

 

Best

Darek

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

By the way, please disentangle the logic of the Excel formula. I don't think anyone would want to do it themselves - too much work. You have to state the algorithm in a clear, unambiguous way so that the rules for calculation are easily understood. Then and only then will people start working on this 🙂

 

Best

Darek

Okay so basically the table returns all the invoices the company has in accounts receivable, which means that one accountNumber can have several invoices. Taking that into account the pseudo code would be something like this

 

If the sum of the debt per client < 0 (SumIf in the excel formula above to sum all the debt from the same customer)

Then client has a "credit balance"

Else if debtUSD < 0 and document type is AA or BB or CC or DD

Then client has "Unapplied Payment"

Else return same value as the column timeInterval

 

I hope this is helpful and thanks for the tip about Switch function

Anonymous
Not applicable

OK.

 

Classification = 
var __currentAccountNumber = 'Accounts Receivable'[Account Number]
var __currentDebt = 'Accounts Receivable'[Debt USD]
var __currentInvoiceType = 'Accounts Receivable'[Invoice Type]
var __currentTimeInterval = 'Accounts Receivable'[Time Interval]
var __sumOfDebtForCurrentClient = 
    SUMX(
        FILTER(
            'Accounts Receivable',
            'Accounts Receivable'[Account Number] = __currentAccountNumber
        ),
        'Accounts Receivable'[Debt USD]
    )
var __classification =
    SWITCH( TRUE(),

        __sumOfDebtForCurrentClient < 0, 
            "Credit Balance",
            
        __currentDebt < 0 && __currentInvoiceType in {"AA", "BB", "CC", "DD"},
            "Unapplied Payment",

        // else
        __currentTimeInterval
    )
return
    __classification

This is your formula for the Classification Column in your table.

 

If this is not performant enough, then here's a variation on this topic. Just change these lines:

 

var __sumOfDebtForCurrentClient = 
    SUMX(
        FILTER(
            'Accounts Receivable',
            'Accounts Receivable'[Account Number] = __currentAccountNumber
        ),
        'Accounts Receivable'[Debt USD]
    )

to these

 

var __sumOfDebtForCurrentClient =
    CALCULATE(
        SUM( 'Accounts Receivable'[Debt USD] ),
        ALLEXCEPT( 
            'Accounts Receivable',
            'Accounts Receivable'[Account Number] 
        )
    )

Not sure which one would be faster on a big table.

 

By the way, here's the Excel formula (which seems to implement a different logic than the one you've described), courtesy of http://excelformulabeautifier.com/:

=IF(
    IF(
        [@group] <> "CASH",
        SUMIF(
            [accountNumber],
            [@accountNumber],
            [debtUSD]
        )
    ) < 0,
    "Credit Balance",
    IF(
        AND(
            [@[debtUSD]] < 0,
            OR(
                [@[invoiceType]] = "AA",
                [@[invoiceType]] = "BB",
                [@[invoiceType]] = "CC",
                [@[invoiceType]] = "DD",
                [@[invoiceType]] = "EE",
                [@[invoiceType]] = "WV"
            )
        ),
        "Unapplied Payment",
        IF(
            OR(
                [@[collectionsAgent]] = "JohnDoe"
            ),
            "Legal",
            [@[timeInterval]]
        )
    )
)

But I've implemented the logic you've described 🙂

 

Best

Darek

Hello Darek, just a quick follow up:

 

Power Bi is giving me an issue with the declared variables with the following error:

 

A single value for column 'Account Number' in table 'Accounts Receivable' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

 

Same thing happens with the other variables. Do you happen to know any fix for this?

 

Regards

Anonymous
Not applicable

I did say: This is your formula for the Classification column in your table. This is not a measure.

 

Are you trying to use any of the formulas as measures? If you do, then you'll get this error.

 

One last thing: READ WHAT I'VE WRITTEN BEFORE WELL AND TRY TO UNDERSTAND IT. Then you'll have no problems. When you read things, read them carefully with a full understanding. Don't glance over text. READ UNTIL YOU FULLY UNDERSTAND WHAT'S BEEN EXPRESSED IN THERE.

 

Best

Darek

Hi Darek!

 

Yeah I see the mistake. When I was trying to add it as a column it returned the error "Token Eof expected". But I was trying to add the column from the query editor rather than with the Add column button in the ribbon tab, in my mind they were the same thing.

 

Thanks again now it's fully working.

 

Regards

Anonymous
Not applicable

Good 🙂

 

Best
Darek

It's working fine! Both sumx and switch formulas were really helpful and that excel formula beautifier will be helpful.

 

Thankyou,

MS

Anonymous
Not applicable

See this:

 

SWITCH in DAX

 

Best

Darek

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors