Count Distinct Text Column

[spandy34]

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?

[QuentinBl · Accepted solution]

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

[v-kaiyue-msft · Accepted solution]

Hi @spandy34 ,

As @QuentinBl  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.

[QuentinBl · Accepted solution]

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