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))
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.
