topic Re: Distinctcount exclude duplicates in DAX Commands and Tips

Distinctcount exclude duplicates

dannytan1112 — Tue, 03 Jan 2023 03:11:52 GMT

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

Re: Distinctcount exclude duplicates

tamerj1 — Tue, 03 Jan 2023 03:36:14 GMT

Hi dannytan1112

you can try

Count =
SUMX (

VALUES ( 'Table'[Column] ),

IF (

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

)

Re: Distinctcount exclude duplicates

dannytan1112 — Tue, 03 Jan 2023 03:45:29 GMT

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

Re: Distinctcount exclude duplicates

Jihwan_Kim — Tue, 03 Jan 2023 03:49:41 GMT

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

Re: Distinctcount exclude duplicates

tamerj1 — Tue, 03 Jan 2023 03:52:32 GMT

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

Re: Distinctcount exclude duplicates

dannytan1112 — Tue, 03 Jan 2023 05:45:07 GMT

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

Re: Distinctcount exclude duplicates

Jihwan_Kim — Tue, 03 Jan 2023 06:59:15 GMT

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

Re: Distinctcount exclude duplicates

dannytan1112 — Tue, 03 Jan 2023 07:15:43 GMT

Really thanks,

It works now. Happy New Year