cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
kshre Frequent Visitor
Frequent Visitor

Distinct Count Filter with Metrics

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 UserCountDistinct Count
JAN2019User121
JAN2019User221
JAN2019User311
JAN2019User451
JAN2019User531
FEB2019User141
FEB2019User221
FEB2019User311
FEB2019User511
   219

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 UserCountDistinct Count
JAN2019User121
JAN2019User221
JAN2019User451
JAN2019User531
FEB2019User141
FEB2019User221
   186

I would appreciate any help on this. 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Distinct Count Filter with Metrics

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 ) )

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Distinct Count Filter with Metrics

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 ) )

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
kshre Frequent Visitor
Frequent Visitor

Re: Distinct Count Filter with Metrics

Hi @v-cherch-msft,

 

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