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!

Reply
coolasia
New Member

Powerpivot distinctcount return wrong values

Dear All

 

I am new to powerpivot and trying to convert my excel file worksheet into powerpivot because my data is

getting huge and hard to managed.

 

I need to calculate unique value in a column of my excel. Therefore I use the function in powerpivot below

 

Unique ID:=Distinctcount(Total[ID])

 

It give me a result of Unique ID =4328 (Wrong )

 

Unfortunately this result is wrong.

 

The correct result is 4348 but i cannot get this correct result.

 

My data contain text, numer, -, . ...etc

 

Can someone advise how i can solved this problem ?

 

Thanks

 

regards

Ken 

 

 

3 REPLIES 3
v-chuncz-msft
Community Support
Community Support

@coolasia,

 

Check if you've refreshed the data model.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi 

Thanks for your reply..I have refresh but still same problem

I suspect my problem with powerpivot is due to my data in the excel file.

My excel file consist of data  with number and text. I notice that there is no

problem for my data if it is TEXT, mixture of text & number or number that within 8 digit

  1.  BYTRUCK9010527936  (no problem)
  2. 25308407 (no problem)

But when number become too long like below

  1. 43430660612014 , it will store the data in excel as “4.34307E+13”
  2. 43430660701011, it will store the data in excel as “”4.34307E+13”

When Powerpivot extract the data, it will store it as “4.34307E+13” and count as 1 instead of 2.

 

Any advise how to solve this kind of problem ?

 

regards

Ken

@coolasia,

 

Try to check the steps in the Advanced Editor.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.