Is DISTINCTCOUNT function causing issues when trying to use Analyze feature?

[SaihanSadique]

Will a visual that uses DISTINCTCOUNT in its measure cause issues if you're trying to use the Analyze?

I have checked the microsoft learn page for the Analyze function but it does not say anything about using functions such as DISTINCTCOUNT and similiar functions, Also could you please let me know if there are any other good resources for using dax measure visual which can make use of the insight function efficiently.

Here is an example query which I'm trying to use.

Active Headcount =

DISTINCTCOUNT('Employee Details'[Employee ID])

[Anonymous]

Hi @SaihanSadique,

I just wanted to follow up on your thread. If the issue is resolved, it would be great if you could mark the solution so other community members facing similar issues can benefit too.
If not, don’t hesitate to reach out, we’re happy to keep working with you on this.

Regards,

Vinay Pabbu

[v-kpoloju-msft]

Hi @SaihanSadique,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.

[v-kpoloju-msft]

Hi @SaihanSadique,

Just wanted to follow up one last time. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

[danextian · Accepted solution]

hi @SaihanSadique 

If you are refering to Analyze in Excel, DISTINCTCOUNT works fine. 

Also could you please let me know if there are any other good resources for using dax measure visual which can make use of the insight function efficiently. This is unclear. What do you mean? You use DISTINCTCOUNT if you are trying to count the distinct values of a column. Bear in mind that if you use this in a table or matrix, it will be evaluated for each distinct value of the row or column category and at the total level for the whole of the table  so the sum of individual rows may not necessary be equal to the total. In the example below, there are only 4 distinct products at the total level: A, B, C and D. 

The  following will return the same result as DISTINCTCOUNT assuming there are not blank products:

COUNTROWS ( VALUES ( 'table'[product] ) )

COUNTROWS ( DISTINCT ( 'table'[product] ) )

[pankajnamekar25]

Hello @SaihanSadique 

try this DAX

ActiveHeadcountFlag = SUMX(

    VALUES('Employee Details'[Employee ID]),

    1

)

Thanks

 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.