Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
We are trying to do a distinct count based on different criterias.
Basically, I have Document IDs and Client IDs. A Document ID can be attached to multiple Client IDs and a Client ID can have mutiple Document IDs.
Client ID / Document ID
DEF / 222
DEF / 333
DEF / 444
GHI / 222
GHI / 333
In that example, a distinct count of client IDs or Document IDs would give me 2 and 3, respectively. Still, because all Document IDs of GHI are also Document to DEF, my distinct count should be 1.
2nd example :
Client ID / Document ID
DEF / 222
DEF / 333
DEF / 444
GHI / 222
GHI / 111
In that 2nd example, a distinct count of client IDs or Document IDs would give me 2 and 3, respectively again. Still, because not all Document IDs of GHI are also Document to DEF, my distinct count should be 2.
Thank you,
Solved! Go to Solution.
The numbers in your 2nd example aren't matching up with my expectation of what you want. I get either 3 or 1. But regardless, what you want is something along the lines of:
Measure = VAR __table1 = DISTINCT(SELECTCOLUMNS(FILTER(Table1,[Client ID]="DEF"),"__DocID",[Document ID])) VAR __table2 = DISTINCT(SELECTCOLUMNS(FILTER(Table1,[Client ID]="GHI"),"__DocID",[Document ID])) RETURN COUNTROWS(EXCEPT(__table1,__table2) + COUNTROWS(EXCEPT(__table2,__table1))
The numbers in your 2nd example aren't matching up with my expectation of what you want. I get either 3 or 1. But regardless, what you want is something along the lines of:
Measure = VAR __table1 = DISTINCT(SELECTCOLUMNS(FILTER(Table1,[Client ID]="DEF"),"__DocID",[Document ID])) VAR __table2 = DISTINCT(SELECTCOLUMNS(FILTER(Table1,[Client ID]="GHI"),"__DocID",[Document ID])) RETURN COUNTROWS(EXCEPT(__table1,__table2) + COUNTROWS(EXCEPT(__table2,__table1))