Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |