Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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))
Solved! Go to Solution.
@Anonymous
SumValueFilters = CALCULATE ( SUM ( Table1[Value] ), FILTER ( Table1, LEFT ( Table1[Cust_ref1] ) <> "W" && Table1[Cust_ref1] <> "JTPD" ) )
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)
CALCULATE(SUM(RMA[Credit Memo]),filter(table, RMA[CUST_REF1]<>"JTPD" && SEARCH("W",RMA[CUST_REF1],1,0) > 0) )
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".
@Anonymous
SumValueFilters = CALCULATE ( SUM ( Table1[Value] ), FILTER ( Table1, LEFT ( Table1[Cust_ref1] ) <> "W" && Table1[Cust_ref1] <> "JTPD" ) )
Thank you for your help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |