Forum Discussion
Distinctcount exclude duplicates
Hi,
I have a question to only distinct count values that is not duplicate.
For example i have a column with "apple", "apple", "banana", "orange", and "cucumber". I want to return the value of 3 because "apple" has duplicate value.
My measure is
Count =
Var Column = 'Table'[Column]
RETURN
CALCULATE(
DISTINCTCOUNT(Column),
ALLSELECTED(Column)
)
It doesnt work for me. Thanks a lot
Hi,
Thank you for your message.
I am not sure how your desired outcome of the visualization looks like, but please check the below picture and the attached pbix file.
Count distinct only measure: = COUNTROWS ( FILTER (DISTINCT( Data[Name] ), CALCULATE ( COUNTROWS ( Data ) ) = 1 ) )
7 Replies
Hi dannytan1112
you can try
Count =
SUMX (VALUES ( 'Table'[Column] ),
IF (
COUNTROWS ( CALCULATETABLE ( 'Table' ) ) = 1,
1
)
)
Hi,
Thanks for your very prompt help, although it shows no error but it produce blank row in my case.
Any idea where i might have mistaken?
Thank you so much
Hi dannytan1112
Not sure about the current filter context but you may also tryCount = SUMX ( ALLSELECTED ( 'Table'[Column] ), IF ( COUNTROWS ( CALCULATETABLE ( 'Table' ) = 1, 1 ) )
Hi,
I am not sure how your data model looks like, but please check the below picture and the attached pbix file.
Count distinct only measure: = COUNTROWS ( FILTER ( Data, CALCULATE ( COUNTROWS ( Data ) ) = 1 ) )Hi Kim,
Really thanks for the pbix. I am still wondering how you can make a measure with the reference of Data (Table name) not category as the column name. I have so many columns, and i want to make measure to return a value of 3.
On my case i want the Fruit return a measure of 3 and Vegetable return value of 2 . Really thanks
Let say
Category Name Fruit Apple
Fruit Apple
Fruit Banana Fruit Orange Fruit Strawberry Vegetable Cucumber Vegetable Cauliflower Vegetable Broccoli Vegetable Broccoli Hi,
Thank you for your message.
I am not sure how your desired outcome of the visualization looks like, but please check the below picture and the attached pbix file.
Count distinct only measure: = COUNTROWS ( FILTER (DISTINCT( Data[Name] ), CALCULATE ( COUNTROWS ( Data ) ) = 1 ) )
