Forum Discussion
Sum columns filtering table on multiple containsstring values
Hi,
Using the table attached as an example 'Financial_Tab'.
| Row ID | CBS Code | CBS Name | Budget |
| 1 | 1.2.3.21 | NOP1 EX1 | £ 10 |
| 2 | 1.2.3.21 | NOP2 EX1 | £5 |
| 3 | 1.2.4.21 | NOP2 EX1 | £15 |
| 4 | 1.2.4.21 | NOP4EX1 | £25 |
| 5 | 1.2.4.21 | NOP2EX1 | £5 |
| 6 | 1.2.4.21 | NOP3 EX1 | £15 |
| 7 | 1.2.4.21 | NOP4 EX1 | £10 |
| 8 | 1.2.4.22 | NOP1EX1 | £5 |
| 9 | 1.2.3.22 | NOP2EX1 | £15 |
| 10 | 22.1.2.4.22 | NOP2EX1 | £10 |
I want to sum [Budget] based on whether "1.2.4" is within [CBS Code], & where "NOP1" or "NOP2" or "NOP3" is within [CBS Name]. Returning the bold rows above.
My primary issue is looking for a succinct and functional way of searching for NOP1/NOP2/NOP3 in one go. I'm returning blank results. I think IN {} could be used but I cant get it to work with containsstring.
I really appreciate any help.
Thanks
Hi NMOORE
Check this measure:
AA = SUMX ( FILTER ( 'Table', CONTAINSSTRING ( 'Table'[CBS Code], "1.2.4" ) && LEFT ( 'Table'[CBS Name], 4 ) IN { "NOP1", "NOP2", "NOP3" } ), [Budget] )If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
3 Replies
Hi NMOORE
Check this measure:
AA = SUMX ( FILTER ( 'Table', CONTAINSSTRING ( 'Table'[CBS Code], "1.2.4" ) && LEFT ( 'Table'[CBS Name], 4 ) IN { "NOP1", "NOP2", "NOP3" } ), [Budget] )If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Thank you very much, I will accept as solution (but wasn't sure if that will close this case!?) but either way I just wanted to ask one more question.
Can it just use the IN function to search anywhere in the CBS Name for NOP1 NOP2 NOP3, rather than search the first 4 left characters, as I can't guarantee they will always be there. I tried to remove the LEFT part but it didn’t like it.
Thanks againYes, If my solution works, it would be better accepte it as the solution to help the other members find it more quickly and close the case.
You can change that to CONTAINSSTRING with some OR to cover those 3 conditions.If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn | Twitter | Blog | YouTube
