Forum Discussion

kshre's avatar
kshre
Frequent Visitor
7 years ago
Solved

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. 

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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,

2 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    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,