topic Count Distinct Text Column in DAX Commands and Tips

Count Distinct Text Column

spandy34 — Fri, 22 Mar 2024 17:22:03 GMT

I am trying to use a card (z No of Roles) to count the distinct number of roles. As you will see there is only one role - Project Delivery Manager (PDM). I have used the following DAX for the card but they both return 2 instead of 1. Any ideas:-

z_No of Roles = COUNTROWS(DISTINCT('Tasks Main'[Role]))

z_No of Roles = DISTINCTCOUNT('Tasks Main'[Role])

Any ideas why I am getting 2 instead of 1?

Re: Count Distinct Text Column

Anonymous — Fri, 22 Mar 2024 18:04:13 GMT

Hello,

I think that DISTINCTCOUNT should do the work.

Can you show your model and relationships ?
And can you verify that the value are truly equal ? (right click -> copy value). Check that there is no space behind the value for exemple

Re: Count Distinct Text Column

Anonymous — Mon, 25 Mar 2024 02:49:39 GMT

Hi spandy34 ,

As Anonymous said, sometimes text values that look the same can contain trailing spaces or invisible characters, causing them to be counted as different values.

Also, if you have a blank or null value in the "Role" column, that value will be counted as a non-duplicate, resulting in a count of 2. So, I created the following table with a space at the left and right ends of the second row of data, and a blank row in the fourth row.

At this point, use the expression

z_No of Roles = COUNTROWS(DISTINCT('Table'[role]))

The result of this calculation is 3.

So you can use the TRIM function and exclude blank values to solve both problems.

z_No of Roles2 = COUNTROWS( DISTINCT( SELECTCOLUMNS( FILTER('Table', 'Table'[role] <> BLANK()), "TrimmedRole", TRIM('Table'[role]) ) ) )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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