Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
@roshak , Try a measure like
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.
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!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |