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")) &&
'_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"
)

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"
    )
)

     

     

3 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    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