Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!!!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |