topic Re: Calculate with multiple contains filters in DAX Commands and Tips

Calculate with multiple contains filters

NMOORE — Thu, 10 Nov 2022 17:16:06 GMT

Hi,

I'm trying to sum one column based on subsequent filers in DAX. Hopefully the below demonstrates what I want to do:

Tier 1 Total =

CALCULATE(

SUM(Table_A [Budget]),

FILTER(Table_A,CONTAINSSTRING(Table_A[CBS Code],"1.1.2.4")),

FILTER(Table_A,CONTAINSSTRING(Table_A[CBS Name], "NOP1")),

FILTER(Table_A,CONTAINSSTRING(Table_A[CBS Name], "NOP2")),

FILTER(Table_A,CONTAINSSTRING(Table_A[CBS Name], "NOP3")))

If I use the the formula in bold it's fine. However I want to include multiple contain filters on [CBS name] and include all 3. I can find what to do if they are straight forward filters but as I'm using the contains feature it makes it a bit trickier. I've tried || and IN functions and failed.

I could probably make it work creating multiple variables and summing them together but it feels like it should be easier.

I really appreciate any help.

Thanks

Re: Calculate with multiple contains filters

Anonymous — Fri, 11 Nov 2022 02:33:14 GMT

Hi NMOORE ,

Here are the steps you can follow：

1. Create calculated column.

Tier 1 Total = var _1={"NOP1","NOP2","NOP3"} return SUMX(FILTER(ALL('Table'), CONTAINSSTRING('Table'[CBS Code],"1.1.2.4")&& 'Table'[CBS Name] in _1),[Budget])

2. Result:

If the results don't meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: Calculate with multiple contains filters

NMOORE — Fri, 11 Nov 2022 11:13:09 GMT

Thanks alot for your reply. I would upload a PBIX file but it won't let me. It's returning a blank on my data.

If you add text at the end of CBS code and CBS name values on your table as per below it wont filter accordingly. I'm not sure why the contains string function is not working?

CBS Code	CBS Name	Budget
1.1.2.3.21	NOP1 EX1	1
1.1.2.3.21	NOP2 EX1	2
1.1.2.3.21	NOP2 EX1	5
1.1.2.4.21	NOP1EX1	6
1.1.2.4.21	NOP2EX1	4
1.1.2.4.21	NOP3 EX1	7
1.1.2.4.21	NOP4 EX1	1
1.1.2.4.222.1	NOP1EX1	5
1.1.2.4.222.1	NOP2EX1	5

Thanks again