Forum Discussion
Ignore rows where fact doesn't sum to zero?
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.
3 Replies
- amitchandak
Super User
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])) ))returncalculate(sum(Data1[FACT]),filter(Data1, _sum >0))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!
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.
