Distinct count based on multiple criterias (and and/or criterias)

[mickbergeron]

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,

[Greg_Deckler · Accepted 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))