Forum Discussion

PowerAutomater's avatar
PowerAutomater
Icon for Helper IV rankHelper IV
1 year ago
Solved

Count 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...
Need Help
Tips and Tricks
Tutorial Requests
  • ryan_mayu's avatar
    ryan_mayu
    1 year ago

    PowerAutomater 

    you can try to create a measure 

    Measure = CALCULATE(DISTINCTCOUNT('Table'[date]),FILTER('Table',not(ISBLANK('Table'[date]))))
PowerAutomater's avatar
PowerAutomater
Icon for Helper IV rankHelper IV
1 year ago

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's avatar
ryan_mayu
Icon for Super User rankSuper User
1 year ago

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's avatar
    PowerAutomater
    Icon for Helper IV rankHelper IV
    1 year ago

    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.