Count Distinct with condition across partition

[Mansfig2]

Hey guys,

I'm struggling to find a DAX based solution to this problem I'm having, this is the table I have:

How would I count the number of distinct values within column 2, grouping within the ID in the first column?

The count should look something like this: 

Thanks for the help! 

George

[Anonymous · Accepted solution]

Hi @Mansfig2 ,

You can create a calculated column as below:

CountDistinct = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[ID2] ),
    FILTER ( 'Table', 'Table'[ID1] = EARLIER ( 'Table'[ID1] ) )
)

Best Regards