Filter within filter?

[lazarus1907]

Hi,
I'm struggling to do this and I suspect the solution will involve nested filters, but I'm not getting any success so far.  Let me illustrate the problem with an example, because it's tricky to explain:
This is the MAIN TABLE:

Code	Class	Value
1	A	0
1	A	0
1	A	2
1	B	6
1	B	3
1	B	2
2	A	0
2	A	0
2	B	11
2	B	13
3	A	0
3	A	0
3	B	77
4	A	0
4	A	0
4	B	17
4	B	19
5	A	0
5	A	0
5	A	3
5	A	0
5	A	0
5	B	21
5	B	23
5	B	27

If we add the Values filtering by class = 'A' for each Code, we get the virtual table SUB_TABLE_1:

Code	Value
1	11
2	24
3	77
4	36
5	71

If we add the Values filtering by class = 'B' for each Code, we get the virtual table SUB_TABLE_2:

Code	Value
1	2
2	0
3	0
4	0
5	3

What I want to do is add only the rows of the SUB_TABLE_1 for the Codes where the sum of the values of SUB_TABLE_2 is not zero, ie. only Code = 1 (Value = 2) and Code  = 5 (Value = 3). In other words, something like this:

Code	Value
1	11
5	71
TOTAL:	82

The original table is obviously much bigger, but what I am trying to get is this kind of total (82 in this example) by adding all the values for class A in the MAIN TABLE, only if the sum of the values for each particular Code for class B is not zero. I am not interested in creating any intermediate tables (eg. SUB_TABLE_1 & SUB_TABLE_2) if this can be avoided through DAX -I just need the total (82 in this example).
I hope this makes sense.
I would be very grateful If anyone has any suggestion or solution.

Thanks

[lbendlin · Accepted solution]

There are multiple ways to achieve this.

In Power Query you can create reference queries and then group them as needed.

In DAX you can create calculated columns that indicate inclusion/exclusion flags,  or you can create temporary tables, join them as needed ad then aggregate the results.

Which approach do you prefer, Power Query or DAX?

BTW, your first two sample outcomes for A and B are the other way round.