Distinct Count Filter with Metrics

[kshre]

Hello,

I need help with a Metrics that will do a Distinct Count of a user column with a filter of Count of User > 1.

The table below is and example the regular distinct count. Both distinct count and count are metrics.

Month	Year	User	Count	Distinct Count
JAN	2019	User1	2	1
JAN	2019	User2	2	1
JAN	2019	User3	1	1
JAN	2019	User4	5	1
JAN	2019	User5	3	1
FEB	2019	User1	4	1
FEB	2019	User2	2	1
FEB	2019	User3	1	1
FEB	2019	User5	1	1
			21	9

Below is what I would want (Distinct Count). I have tried the following formula so far with no success. It would give me the regular distinct count.

RepeatedUsers =

CALCULATE(DISTINCTCOUNTNOBLANK(Table[User]), FILTER(Table, COUNTA(Table[User])> 1))

Month	Year	User	Count	Distinct Count
JAN	2019	User1	2	1
JAN	2019	User2	2	1
JAN	2019	User4	5	1
JAN	2019	User5	3	1
FEB	2019	User1	4	1
FEB	2019	User2	2	1
			18	6

I would appreciate any help on this. 

[v-cherch-msft · Accepted solution]

Hi @kshre 

You may create a measure like below:

Count = 
VAR a =
    SUMMARIZE (
        Table3,
        Table3[Year],
        Table3[Month],
        Table3[User],
        "b", COUNTA ( Table3[User] )
    )
RETURN
    COUNTROWS ( FILTER ( a, [b] > 1 ) )

Regards,

[kshre]

Hi @v-cherch-msft,

Thank you for your help, that measure does exactly what I need.