Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

DAX DISTINCT and DISTINCTCOUNT does not ignore duplicates

Applying DISTINCT and DISTINCTCOUNT does not remove duplicates from Int64 column in Power BI Desktop.

 

The returned result is the same as the total count of records.

 

For example, this expression returns 2 for me:

 

Duplicates count after Distinct = COUNTROWS(FILTER(DISTINCT(LabelTracking[Order Item Id]), [Order Item Id] == 290363))
 
Table has 14134 recrods, some with duplicated Order Item Id (e.g. 290363). No matter if I try to use DISTINCT or DISTINCTCOUNT over the Order Item Id column, I get the same total count 14134.
 
No other filters used.
Status: New
Comments
kost
Frequent Visitor

I have found a workaround:

 

Labeled Items Count = COUNTROWS(SUMMARIZE(LabelTracking, LabelTracking[Order Item Id], "Ones", 1))
 
gives me the expected distinct counf (measure "Ones" is required to make it working).
kost
Frequent Visitor

Some extra info:

* the report is based on Push Power BI Service dataset

* importing of the report to Power BI Service does not fix the issue

* exporting of the data to csv file and doing the distinct count on top of the csv data works

v-chuncz-msft
Community Support

@kost 

 

I tried the latest version to create a report and it seems to work fine for me.

kost
Frequent Visitor

Hi @v-chuncz-msft ,

I guess this issue might be hard to reproduce. I can reproduce it only on one particular field on my side.

I do not know exactly how can I help you to reproduce it. I am using the APIs to create the dataset and to populate it with data. This setup is hard to replicate.

 

Do you maybe have some hints? Also, feel free to contact me directly.  I will be happy to help you to explore the issue.

kost
Frequent Visitor

I have found a mistake in my data model. I guess, this is the reason.

 

kost_0-1615542270441.png

 

The field is used in a relation and the wrong cardinality is specified for the fields. 

 

I guess, the distinccount somehow relies on this information.

 

Update: the change of the cardinality fixed the issue.