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]))))
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?
Super Usermaybe 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]))))- 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 datesEvent 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.