Forum Discussion
Helper IVCount Distinct not yielding the same as Unique in Excel
you can try to create a measure
Measure = CALCULATE(DISTINCTCOUNT('Table'[date]),FILTER('Table',not(ISBLANK('Table'[date]))))
Super Userpls check if there is any time value in the data field. You can set the data type to datetime to double check. Sometimes, when you set the date format. Two dates looks like the same, however the time values are different. That will be counted as 2 not 1.
Helper IVOk I have selected the field and in the column tools the Data Type is Date, and the Format is d/mm/yyyy. Is that what you meant?
- ryan_mayu
maybe there is another reason. Is there any blank cell in your excel? if the last parameter is true, unique will ignore blank cells. However, powerbi will count that as 1 value.
It's better to provide some sample data to have a further investigation.
- PowerAutomater
Yes that could be the reason. I have just done the following test:
Clearly there should only be 5 unique values but this time it is showing 6 in Excel. And if I replicate the data card in PowerBi it is also showing 6 instead of 5:
I can't delete the blanks from the data source as they are important for other stats, so how would I go about ignoring them so that extra 1 isn't added each time? This is the filter I am using in my actual dashboard:- ryan_mayu
you can try to create a measure
Measure = CALCULATE(DISTINCTCOUNT('Table'[date]),FILTER('Table',not(ISBLANK('Table'[date]))))