Ignore rows where fact doesn't sum to zero?

[roshak]

Given the following table:

ID	DIM1	DIM2	FACT	COMMENT
1	A	TEST	-5.00	SHOULD BE HIDDEN
2	A	TEST	5.00	SHOULD ALSO BE HIDDEN
3	B	ABC	-1.30	IGNORED NOTES
4	B	ABC	1.30	MORE IGNORED NOTES
5	C	TEST	34.00	SHOULD BE VISIBLE
6	A	TEST2	-2.20	NOT SHOWN
7	A	TEST2	2.20	MORE NOT SHOWN
8	B	ABCD	7.20	SHOULD BE SEEN
9	B	ABC	1.30	VISIBLE

What is the best technique(s) to "ignore rows whose facts cancel out"? 

Prefered result:

ID	DIM1	DIM2	FACT	COMMENT
5	C	TEST	34	SHOULD BE VISIBLE
8	B	ABCD	7.20	SHOULD BE SEEN
9	B	ABC	1.30	VISIBLE

I had considered doing it in PowerQuery via a groupby DIM1/DIM2/SUM(FACT) WHERE SUM(FACT) is not 0 and then rejoin back to the previous rows to bring the ID and COMMENT back in.

However, I thought there may be a DAX alternative as well.

Link to example

[amitchandak]

@roshak , Try a measure like

measure =

var _sum = calculate(sum(Data1[FACT]), filter(allselected(Data1), Data1[dim1] = max(Data1[dim1])  && Data1[dim2] = max(Data1[dim2]) && abs(Data1[FACT]) = abs(MAX(Data1[FACT])) ))

return

calculate(sum(Data1[FACT]),filter(Data1, _sum >0))

[roshak]

I thought this was exactly what I needed until my real data surfaced another scenario.  In some cases there will be multiple rows for the DIMs and any that aren't negated by a single exact match should remain.  

ID	DIM1	DIM2	FACT	COMMENT
1	A	TEST	-5.00	SHOULD BE HIDDEN
2	A	TEST	5.00	SHOULD ALSO BE HIDDEN
3	B	ABC	-1.30	IGNORED NOTES
4	B	ABC	1.30	MORE IGNORED NOTES
5	C	TEST	34.00	SHOULD BE VISIBLE
6	A	TEST2	-2.20	NOT SHOWN
7	A	TEST2	2.20	MORE NOT SHOWN
8	B	ABCD	7.20	SHOULD BE SEEN
9	B	ABC	1.30	VISIBLE

Desired result:

ID	DIM1	DIM2	FACT	COMMENT
5	C	TEST	34	SHOULD BE VISIBLE
8	B	ABCD	7.20	SHOULD BE SEEN
9	B	ABC	1.30	VISIBLE

I have updated the sample file and will update the original post.

[roshak]

that is exactly what I was looking for.  I used a similar approach in PowerQuery-M but needed the flexibility to have more than one type of MEASURE that allowed for different DIM column evaluations for the same logic.  (i.e., one measure includes MONTH in the dimensions and another does not).  thanks!