SUM , FIlter, Containsstring DAX Formula

[juhoneyighot]

Hello!

I have this sum dax formula with the following filters

0RevActTransactions2 = CALCULATE(
SUM('_Facts'[Amt])*-1,
FILTER(_Facts,CONTAINSSTRING(_Facts[LOOKUPLegacyTransType],"INVOICE")) &&
'_Facts'[FH_CurrentYN] = "Yes" &&
'_Facts'[FH_Type] = "28 Legacy AS Transactions" &&
'_Facts'[GLAccountCategory] = "400 SALES"
)

However, I have to add another filter CONTAINSSTRING(_Facts[LOOKUPLegacyTransType],"CREDIT MEMO" which is the same field where the "INVOICE" has been filtered.

Any suggestions how I could do the formula. I have done this but I get the incorrect result

0RevActTransactions2 = CALCULATE(
SUM('_Facts'[Amt])*-1,
FILTER(_Facts,CONTAINSSTRING(_Facts[LOOKUPLegacyTransType],"INVOICE" && CONTAINSSTRING(_Facts[LOOKUPLegacyTransType],"CREDIT MEMO"))) &&
'_Facts'[FH_CurrentYN] = "Yes" &&
'_Facts'[FH_Type] = "28 Legacy AS Transactions" &&
'_Facts'[GLAccountCategory] = "400 SALES"
)

[Bibiano_Geraldo · Accepted solution]

Hi @juhoneyighot ,
The issue you're encountering is because the CONTAINSSTRING function is not properly combining both conditions in the FILTER function. When you use && inside CONTAINSSTRING, it doesn't work as expected because CONTAINSSTRING expects a single condition to evaluate, not multiple conditions combined with logical operators.

Here's the corrected version of your DAX formula:

RevActTransactions2 = CALCULATE(
    SUM('_Facts'[Amt]) * -1,
    FILTER(
        _Facts,
        (CONTAINSSTRING(_Facts[LOOKUPLegacyTransType], "INVOICE") || CONTAINSSTRING(_Facts[LOOKUPLegacyTransType], "CREDIT MEMO")) &&
        '_Facts'[FH_CurrentYN] = "Yes" &&
        '_Facts'[FH_Type] = "28 Legacy AS Transactions" &&
        '_Facts'[GLAccountCategory] = "400 SALES"
    )
)

[Greg_Deckler]

@juhoneyighot Try:

0RevActTransactions2 = CALCULATE(
SUMX(,
  FILTER(
    _Facts, 
    CONTAINSSTRING(_Facts[LOOKUPLegacyTransType],"INVOICE") && 
    CONTAINSSTRING(_Facts[LOOKUPLegacyTransType],"CREDIT MEMO") &&
    '_Facts'[FH_CurrentYN] = "Yes" &&
    '_Facts'[FH_Type] = "28 Legacy AS Transactions" &&
    '_Facts'[GLAccountCategory] = "400 SALES"
  ),
  '_Facts'[Amt]
)*-1

[juhoneyighot]

@Greg_Deckler It doesnt show any value