Distinct Count not working in DIRECT QUERY

[Anonymous]

Hello Everyone,

I see an issue with distinct count in direct query reports.

I wanted to get distinct count of ACRDID in the below image.

The total distinct are just 21 but it is showing the total number of ACRDID(2423454). It is not grouping with the other columns.

If I just keep the ID it only gives the 21 values but if I change it to count to gives me the wrong number. 

[Anonymous]

Yes thats not working. Its giving me the total count of acrdid rather than acrdid associated with the account

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please check the limitation of distinct count function in offical blog.

For reference: Remarks

• This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

If you use distinct count function in a measure, I think it will work.

Here is my test. Distinct count works in my measure.

Or you can try distinct count in value field.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Measure works but doesnt give me the right now. Its not grouping by its adding up all the acrdids

for a paticular customer I only have 8 acrdids and total if I remove is 2419547

Instead of showing 8 its shows 2419547

[amitchandak]

@Anonymous , Are you using

distinctcount(Table[ACRDID])