Forum Discussion

spandy34's avatar
spandy34
Responsive Resident
2 years ago
Solved

Count Distinct Text Column

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?

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.