Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Tearing my hair out on this.
I have a table with many rows that when a distinct count is performed across the first pair of columns produces the result:
A|Z|15
A|Y|10
B|Y|1
A|X|6
A|W|10
B|W|2
ie. above, there are 15 record in the original table with A|Z|.
But i want to subtract the distinct count for 'B' from 'A'
Thus the |A|W| beciomes 8 rather than 10. See below.
A|Z|15
A|Y|9
B|Y|1
A|X|6
A|W|8
B|W|2
This needs to be done depending on slicers, so i cant do it with an excluding join in the SQL query.
Im not very good with DAX, and to date i have gotten by with doing this sort of thing in SQL queries.
Solved! Go to Solution.
Hi @al4x,
Based on my test, you should be able to use the formula below to create a new measure to subtract the distinct count for 'B' from 'A' in your scenario.
Measure = VAR currentColumn1 = FIRSTNONBLANK ( Table1[Column1], 1 ) VAR currentColumn2 = FIRSTNONBLANK ( Table1[Column2], 1 ) RETURN IF ( currentColumn1 = "A", DISTINCTCOUNT ( Table1[Column3] ) - CALCULATE ( DISTINCTCOUNT ( Table1[Column3] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Column1] = "B" && Table1[Column2] = currentColumn2 ) ), DISTINCTCOUNT ( Table1[Column3] ) )
Here is the sample pbix file for your reference.
Regards
Hi @al4x,
Based on my test, you should be able to use the formula below to create a new measure to subtract the distinct count for 'B' from 'A' in your scenario.
Measure = VAR currentColumn1 = FIRSTNONBLANK ( Table1[Column1], 1 ) VAR currentColumn2 = FIRSTNONBLANK ( Table1[Column2], 1 ) RETURN IF ( currentColumn1 = "A", DISTINCTCOUNT ( Table1[Column3] ) - CALCULATE ( DISTINCTCOUNT ( Table1[Column3] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Column1] = "B" && Table1[Column2] = currentColumn2 ) ), DISTINCTCOUNT ( Table1[Column3] ) )
Here is the sample pbix file for your reference.
Regards
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |