Powerpivot distinctcount return wrong values

[coolasia]

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 

[v-chuncz-msft]

@coolasia,

Check if you've refreshed the data model.

[coolasia]

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

[v-chuncz-msft]

@coolasia,

Try to check the steps in the Advanced Editor.