Count distinct not returning correct value

[derekgould]

I have a power bi report using a web url to a stored procedure.

I have a column called CommunicationID, which contains a text string. In the Table View, PBI shows that this column has 6092 distinct values (out of about 180k records). I've confirmed this number in the source data as well.

However, when I show this column on screen, using the aggregate Count(distinct), it returns 6091.

Any suggestions as to why? I've read in another thread that PBI will estimate values above a certain threshold; is that the issue here? If so is there a way to turn off estimation and have it always show the actual number?

[Anonymous]

Your solution is great Ritaf1983
Hi, @derekgould 

Do you get answers to your questions from Super user's reply?
If you have already fixed the problem, you can mark a helpful reply as a solution. So that other members of the community can quickly find a solution if they encounter similar problems.
Your cooperation will work with us to promote the development of the community. Thank you again!

Best Regards

Jianpeng Li

[Ritaf1983]

Hi @derekgould 

The discrepancy you're encountering between the distinct count in the Table View and the count shown using an aggregate in Power BI could be due to several reasons. Let's go through the potential causes and solutions:

1. Data Refresh and Accuracy
Refresh Status: Ensure that your data is fully refreshed in Power BI. Sometimes, discrepancies can arise if the report or dataset hasn't been refreshed to reflect the latest data from the source.
Data Type Consistency: Verify that the data type for the CommunicationID column is consistent and correctly set to Text. Inconsistent data types or hidden characters can affect how values are counted.
2. Handling of Null or Blank Values
Null or Blank Values: Check if there are any null or blank values in your CommunicationID column. Sometimes these values can affect the distinct count calculation.
Handling in Visuals: In some cases, visuals may exclude null or blank values from their counts. Verify if your distinct count visual is set to include or exclude these values.
3. Power BI Aggregation and Estimation
Aggregation and Estimation: Power BI does sometimes use approximations or estimations for large datasets to improve performance. This is more common in aggregations and summaries rather than direct counts.
Switching to Exact Count: To ensure you're getting the exact count, you can use DAX (Data Analysis Expressions) measures to calculate distinct counts. Here's how you can create an exact count measure:
DAX
Copy code
ExactDistinctCount = DISTINCTCOUNT('YourTable'[CommunicationID])
Using DAX Measures: Place this measure in your report visual to get the exact count of distinct values.
4. Data Modeling and Relationships
Model Relationships: Ensure that there are no incorrect relationships or filters in your data model that might be affecting the distinct count. Relationships between tables or filters applied elsewhere might influence the results shown in your visuals.
Cross-filtering: Check if any slicers or filters are applied to the visual that might be affecting the distinct count.
5. Visual and Data Model Settings
Check Visual Settings: Ensure that the visual where you’re showing the count is correctly configured to display the exact count. Verify that no additional filters or slicers are affecting the visual.
Check Data Model for Aggregation Settings: In some cases, if you have aggregation settings in your data model, they could affect how distinct counts are displayed.
6. Revalidate Source Data
Source Data Validation: Double-check the source data to ensure that it truly has 6092 distinct values. Sometimes discrepancies might arise due to data anomalies or issues in the source.
Summary
To address the issue:

Verify and Refresh Data: Ensure the data in Power BI is up-to-date and accurately represents the source.
Check Null and Blank Values: Look for any null or blank entries in the column.
Use Exact Count Measures: Implement DAX measures to get exact counts rather than relying solely on visual aggregation.
Review Data Model and Filters: Confirm that no filters or relationships are affecting the count.

If previous post helped, then please consider Accepting it as the solution to help the other members find it more quickly