DAX statement with Ifs and nested SUMIFS

[misunika95]

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 🙂

[Anonymous]

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

[misunika95]

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 · Accepted solution]

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

[misunika95]

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]

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

[misunika95]

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]

Good 🙂

Best
Darek

[misunika95]

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

Thankyou,

MS

[Anonymous]

See this:

SWITCH in DAX

Best

Darek