cancel
Showing results for 
Search instead for 
Did you mean: 
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
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors