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 M...
amitchandak
Super User
Super Userroshak , 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))
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.