Distinctcount exclude duplicates

[dannytan1112]

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

[Jihwan_Kim]

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

[dannytan1112]

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

[Jihwan_Kim · Accepted solution]

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

[dannytan1112]

Really thanks,

It works now. Happy New Year

[dannytan1112]

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

[tamerj1]

Hi @dannytan1112 
Not sure about the current filter context but you may also try

Count =
SUMX (
    ALLSELECTED ( 'Table'[Column] ),
    IF ( COUNTROWS ( CALCULATETABLE ( 'Table' ) = 1, 1 )
)

[tamerj1]

Hi @dannytan1112 

you can try

Count =
SUMX (

VALUES ( 'Table'[Column] ),

IF (

COUNTROWS ( CALCULATETABLE ( 'Table' ) ) = 1,

1

)

)