topic Re: Sum columns filtering table on multiple containsstring values in DAX Commands and Tips

Sum columns filtering table on multiple containsstring values

NMOORE — Sun, 13 Nov 2022 21:08:08 GMT

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

Re: Sum columns filtering table on multiple containsstring values

VahidDM — Sun, 13 Nov 2022 23:50:35 GMT

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!!

LinkedIn | Twitter | Blog | YouTube

Re: Sum columns filtering table on multiple containsstring values

NMOORE — Mon, 14 Nov 2022 11:37:45 GMT

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

Re: Sum columns filtering table on multiple containsstring values

VahidDM — Tue, 15 Nov 2022 06:20:23 GMT

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