Forum Discussion

juhoneyighot's avatar
juhoneyighot
Helper III
1 year ago
Solved

SUM , FIlter, Containsstring DAX Formula

Hello!   I have this sum dax formula with the following filters 0RevActTransactions2 = CALCULATE( SUM('_Facts'[Amt])*-1, FILTER(_Facts,CONTAINSSTRING(_Facts[LOOKUPLegacyTransType],"INVOICE")) &&...
Tips and Tricks
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    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's avatar
Greg_Deckler
Community Champion
1 year ago

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