Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Distinct count with Greater than filter

  HI,   CALCULATE(DISTINCTCOUNT(Data[City]),FILTER('Data',Data'[Employee]>2))   the above formula is not working as expected.  it should  count distinct values of all with  employee >2  , but   ...
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi Anonymous ,

    I have created a sample table like this and basically your initial measure can work normally:

    You can try to create another measure like this and it can also work:

    _Count = 
VAR tab =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), 'Table'[Employee] > 2 ),
        'Table'[Employee],
        'Table'[City]
    )
RETURN
    COUNTX ( FILTER ( tab, [Employee] IN DISTINCT ( 'Table'[Employee] ) ), [City] )

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yingjl's avatar
v-yingjl
Icon for Community Support rankCommunity Support
5 years ago

Hi Anonymous ,

I have created a sample table like this and basically your initial measure can work normally:

You can try to create another measure like this and it can also work:

_Count = 
VAR tab =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), 'Table'[Employee] > 2 ),
        'Table'[Employee],
        'Table'[City]
    )
RETURN
    COUNTX ( FILTER ( tab, [Employee] IN DISTINCT ( 'Table'[Employee] ) ), [City] )

Attached a sample file in the below, hopes to help you.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.