Forum Discussion
Helper IVCount Distinct not yielding the same as Unique in Excel
I have a column of user event dates, and I am using a data card with the field set to Count Distinct so I can get a numerical summary of the unique dates that took place during a specific time period (I have a visual relative date filter set).
However for some reason the data card always yields a + 1 to what the result should be. For example, the manual count is 23, and if in Excel I use the Unique formula on the dates it also yields 23, however in PowerBI the count distinct result is 24 and I am not sure why?
I have tried this on a few samples of the data and the result is consistent so I don't think it is the data. Is there a reason why PowerBi would be adding the extra 1, and where should I look to see if I can remove it?
you can try to create a measure
Measure = CALCULATE(DISTINCTCOUNT('Table'[date]),FILTER('Table',not(ISBLANK('Table'[date]))))
9 Replies
ryan_mayuSuper User
pls 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.
PowerAutomaterOk 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.
Hi PowerAutomater,
Thank you for reaching out to the Microsoft Fabric Forum Community. And aslo Thanks to ryan_mayu for prompt and helpfil responses.
There is no setting in Power BI that forces visuals to ignore blanks in all calculations. The only reliable methods are:
Using FILTER(..., NOT(ISBLANK(...))) in measures.
Cleaning blanks in Power Query (not suitable if you need to retain blanks for integrity).
Thanks & regards,
Prasanna Kumar
