I've attempted to utilize the SQL AVG(##) function to obtain the average of a column generated using SQL COUNT(DISTINCT(##)), or DISTINCTCOUNT(##) in PowerBI (they're both effectively the same thing). However, each attempt results in the error message 'Parameter is not the correct type.' Even when I attempt to employ separate measures in PowerBI, the operation is still restricted.
I've experimented with Google Big Query, PowerBI, and Metabase, but none of them seem to work. It appears that the issue lies in the SQL itself, or at least that's the strong indication. Is there a solution to make this work?