Count Distinct Text Column

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

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.