calculate where does not equal wildcard

[Anonymous]

I'm trying to calculate the total of "Credit Memo" where Cust_Ref1 is not "JTPD" or where Cust_Ref1 starts with a "W".  Here is what I got but it does not work.  Any advice would be appreciated.  Thank you

CALCULATE(SUM(RMA[Credit Memo]),RMA[CUST_REF1]<>"JTPD",RMA[CUST_REF1]<>SEARCH("W",RMA[CUST_REF1],1,0))

[tringuyenminh92]

Hi @Anonymous,

As MSDN, Search method will return starting position of the first text string from the first character of the second text string. So i think RMA[CUST_REF1]<>SEARCH("W",RMA[CUST_REF1],1,0) should be replaced by SEARCH("W",RMA[CUST_REF1],1,0) > 0

If above change is still not working, please put 2 expression inside 

CALCULATE(SUM(RMA[Credit Memo]),RMA[CUST_REF1]<>"JTPD",SEARCH("W",RMA[CUST_REF1],1,0) > 0)

or 

CALCULATE(SUM(RMA[Credit Memo]),filter(table, RMA[CUST_REF1]<>"JTPD" && SEARCH("W",RMA[CUST_REF1],1,0) > 0) )

[Anonymous]

Neither solution seems to work properly.  My SQL query was:

WHERE not CUST_REF1 = 'JTPD' and not CUST_REF1 like 'W%'

and that worked but now I need to include everything in the sql query and use dax formulas to remove the JTPD and starts with "W".

[Vvelarde · Accepted solution]

@Anonymous

SumValueFilters =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER ( Table1, LEFT ( Table1[Cust_ref1] ) <> "W" && Table1[Cust_ref1] <> "JTPD" )
)

[Anonymous]

Thank you for your help!