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.
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
Solved! Go to Solution.
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!!
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 again
Yes, 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
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |