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 need to sum a column if my quote columns "contains" certain text, it may not be exact because data entry people may put their initials at the end. For example, I just wrote this, but it's only part of the number because there are a bunch of these quote numbers with things like AJ, AP, BAF or anything else that I can't possibly know. How do I write "contains" instead of "=". I've tried * at the end and that results in nothing.
FIIK = CALCULATE(SUM('WorkBookBP'[Net Inv Amt]),FILTER('WorkbookBP','Partial Text Column'[Quote Header.Quote Number]="B1-123456789"))
With the above I only get the sum for the one quote number, but there are several more that I need to pick up.
Once I get that figured out I need to layer in 4 other numbers with the same issue.
Thanks for any help you can give
Solved! Go to Solution.
If I understand your formula correctly you would put it after the <>0 before the parentheses, then put (I assume) a second FIND() condition there. Something like
FIIK = CALCULATE( SUM('WorkBookBP'[Net Inv Amt]), FILTER( 'WorkBookBP', FIND( "P1-123456789", 'WorkBookBP'[Quote Header.Quote Number], , 0 ) <> 0 || FIND( "Q2-123456789", 'WorkBookBP'[Quote Header.Quote Number], , 0 ) <> 0 ) )
Proud to be a Super User!
Other than the alphabetic prefixes, are the numbers after the hyphen a fixed length? Of, for that matter, is it always divided into "something-number"? You could use either the LEFT or RIGHT functions depending on the structure of the number.
Proud to be a Super User!
I got this to work for one of the quote numbers, but now I need to add 4 other quote numbers:
FIIK = CALCULATE(SUM('WorkBookBP'[Net Inv Amt]),FILTER(''WorkBookBP',FIND("P1-123456789",'WorkBookBP'[Quote Header.Quote Number],,0)<>0))
That gave me several lines of results which is perfect. Now I can't get an OR to work, I've tried || after the last two parenthesis, I've tried leaving out the ",,0)<>0)) between the II.
How do I write OR?
If I understand your formula correctly you would put it after the <>0 before the parentheses, then put (I assume) a second FIND() condition there. Something like
FIIK = CALCULATE( SUM('WorkBookBP'[Net Inv Amt]), FILTER( 'WorkBookBP', FIND( "P1-123456789", 'WorkBookBP'[Quote Header.Quote Number], , 0 ) <> 0 || FIND( "Q2-123456789", 'WorkBookBP'[Quote Header.Quote Number], , 0 ) <> 0 ) )
Proud to be a Super User!
That worked!! Thank you so much!!!
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |