Count Distinct not yielding the same as Unique in Excel

[PowerAutomater]

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?

[v-pgoloju]

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

[ryan_mayu]

@PowerAutomater 

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.

[PowerAutomater]

Ok 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]

@PowerAutomater 

you can try to create a measure 

Measure = CALCULATE(DISTINCTCOUNT('Table'[date]),FILTER('Table',not(ISBLANK('Table'[date]))))

[PowerAutomater]

Ok I have tried this and it works with the sample data but for some reason when I try to do the same with my actual dashboard both data cards (count & measure) show the same incorrect amount with the +1 added.

I suspect the cause might be another basic filter I have on the data cards which filters the total by the event name. So there are 4 events:

Event 1 = 2 dates

Event 2 = 4 dates

Event 3 = 5 dates

Event 4 = 1 date

Here is where the issues start. With all 4 events selected in the basic filter the data card states the total as 11, even though it should clearly be 12 (2+4+5+1=12). However if I deselect Event 3 the total is 7 which is correct, and if I deselect Event 1 , 2 & 4 the total is also correct at 5. But Event 1 & 3 total at 6 (instead of 7), and Event 2 & 3 total at 9 (correct).

Could it be that some events share the same date so when certain filters are on if there is an overlap between multiple event dates that date gets dropped, resulting in the total number dropping for some event combinations? Otherwise not quite sure what is going wrong.

[ryan_mayu]

I am not sure why this happened.

I guess maybe there are some duplicated dates between different Events

Since you only have several dates, you can try not to use the measure and just display all dates in table. 

if this is the reason, you can create a new column 

then distinct count the new column

[PowerAutomater]

Yes I unfortunately need a single total so a table isn't going to suffice.

The only solution I can think of is to create a measure similar to the above for every event, then create another measure for adding the result of these measures together to get the total. That seems like quite a lot of steps for something that should be pretty straight forward though.

Is there a more global way this could be set once so that the blanks are not causing an issue in every visual added to the dashboard? Eg. like a setting that ignores all blanks by default but doesn't actually delete them since otherwise the data integrity would be effected.