Forum Discussion
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 it is only considering the immediate records which is Employee=3 . its not considering all >2 ( like employee 4,5 ,6...). any suggestions or fixes for this please???
Regards,
Raju.
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.
4 Replies
- mahoneypat
Microsoft Employee
Not sure this is what you mean but this expression with give the count of cities with >2 employees
NewMeasure =
COUNTROWS (
FILTER (
VALUES ( Data[City] ),
CALCULATE (
DISTINCTCOUNT ( Date[Employee] )
) > 2
)
)Regards,
Pat
- Ashish_Mathur
Super User
Hi,
As long as Employee is a column (not a measure) in your Data Table, your formula should work fine. It can be shortened to:
=CALCULATE(DISTINCTCOUNT(Data[City]),Data[Employee]>2)
If this does not work, then share the download link of your file and show the problem clearly.
- amitchandak
Anonymous , Try a measure like
countx(filter(summarize(Table, Data[City], "_1", distinctcount(Data[Employee])),[_1]>2),[City])
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
v-yingjlCommunity Support
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.
