cancel
Showing results for
Search instead for
Did you mean:
Highlighted
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 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.

1 ACCEPTED SOLUTION

Accepted Solutions
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 ) )
```

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

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

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.
Frequent Visitor

## Re: Distinct Count Filter with Metrics

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