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]))))
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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