Incorrect Count Distinct in Table and Visualization

[manoj_0911]

I am facing an issue where the *Count Distinct* for a field (`Connected`) is incorrect in visualizations like tables and decomposition trees. For example, for `Division Name: Bangalore`, the database shows a distinct count of `Connected` as **296**, but Power BI displays a higher value.

The `Offered` field, on the other hand, displays the correct sum in all visualizations. This inconsistency seems specific to the `Connected` field.

Steps Taken:
1. Verified the data in the database, confirming the correct distinct count for `Connected`.
2. Cross-checked with other fields like `Offered`, which are accurate in Power BI visualizations.
3. Tested in different visuals (Table, Decomposition Tree, etc.), but the issue persists.

Attachments:
- Sample Data in Excel
- `.pbix` file for replication

https://docs.google.com/spreadsheets/d/1u5JUN4NtL2L-odbmocS6WPJM8fmb4g9w/edit?usp=sharing&ouid=10765...

https://drive.google.com/file/d/1gdRJV6-MuT-giv4HitPlOzp0ZSKg-g7q/view?usp=sharing

Expected Behavior:
The `Count Distinct` for the `Connected` field should align with the database values and match across visuals.

Environment Details:
- Data Source: [SQL Server]

I would appreciate it if the community could provide insights or solutions to resolve this issue.

[HotChilli · Accepted solution]

There are null values (blanks) in the data so technically the result looks correct but not what you want.

I think you can use DISTINCTCOUNTNOBLANK in a measure for this.

Use +0 if you want to force a 0 result when it would otherwise be blank

[manoj_0911]

Thank you so much for helping me fix this.

[manoj_0911]

The total for the Connected field in the visual seems to be inaccurate. Based on the database query results, DNIS ending with 555 has 295 calls in the database. The other DNIS values shown in Power BI are showing as 0 in the database. Additionally, an extra record appears in Power BI. I have attached the database query results for reference to assist in troubleshooting

[HotChilli]

"Expected Behavior:
The `Count Distinct` for the `Connected` field should align with the database values and match across visuals." - that's a little bit obscure.

Do you mean "the total is not right" for that visual? Is it supposed to be 306 for this visual?