Forum Discussion
Filtering using text found in a column
I have a table as follows.
| Client | price | Line Memo | Date |
| A | 34000 | Outgoing payment -A | 12/2/2022 |
| b | 23000 | Failed to pay | 13/9/2022 |
| c | 21440 | Incoming Payment - C | 1/2/2023 |
| d | 53000 | On hold | 23/7/2022 |
| e | 56000 | Outgoing payment -e | 4/6/2022 |
| f | 12300 | Incoming Payment -f | 6/12/2022 |
| g | 61000 | On transit | 12/1/2023 |
The plan is to do a Sum of the price where the Line memo contains Outgoing Payment or Incoming Payment prefixes and also is for the year 2022.
this is where I am at
though with an error.
Cumulative Balance =
CALCULATE (
SUM ( Table[Price] ),
FILTER (Table, SEARCH(Table [Line Memo] ="Incoming Payments" && (Table [Line Memo] ="Outgoing Payments" )
&& Table[Date] <= "12/31/2022"
)))
Please assist. Thanks.
Try this:
Try both && as shown below and also try > || instead of &&
SEARCH("Incoming Payments" && "Outgoing Payments", Table [Line Memo])Search DAX function | Text functions in Power BI - Power BI Docs
Hi Anonymous ,
Please try below steps:
1. below is my test table
Table:
2. create a measure with below dax formula
Measure = VAR tmp = FILTER ( ALL ( 'Table' ), AND ( OR ( CONTAINSSTRING ( [Line Memo], "Incoming payment" ), CONTAINSSTRING ( [Line Memo], "Outgoing payment" ) ), [Date] <= DATE ( 2022, 12, 31 ) ) ) RETURN SUMX ( tmp, [price] )3. add a card visual with measure
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Try this:
Try both && as shown below and also try > || instead of &&
SEARCH("Incoming Payments" && "Outgoing Payments", Table [Line Memo])Search DAX function | Text functions in Power BI - Power BI Docs
Hi Anonymous ,
Please try below steps:
1. below is my test table
Table:
2. create a measure with below dax formula
Measure = VAR tmp = FILTER ( ALL ( 'Table' ), AND ( OR ( CONTAINSSTRING ( [Line Memo], "Incoming payment" ), CONTAINSSTRING ( [Line Memo], "Outgoing payment" ) ), [Date] <= DATE ( 2022, 12, 31 ) ) ) RETURN SUMX ( tmp, [price] )3. add a card visual with measure
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.