Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
spandy34
Responsive Resident
Responsive Resident

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?

 

spandy34_0-1711127884280.png

 

2 ACCEPTED SOLUTIONS
QuentinBl
Helper I
Helper I

Hello,

I think that DISTINCTCOUNT should do the work. 

QuentinBl_0-1711130868593.png

 



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

View solution in original post

v-kaiyue-msft
Community Support
Community Support

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.

vkaiyuemsft_0-1711334864825.png

 

At this point, use the expression

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

The result of this calculation is 3.

vkaiyuemsft_1-1711334894746.png

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])
)
)
)

vkaiyuemsft_2-1711334938123.png

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.

View solution in original post

2 REPLIES 2
v-kaiyue-msft
Community Support
Community Support

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.

vkaiyuemsft_0-1711334864825.png

 

At this point, use the expression

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

The result of this calculation is 3.

vkaiyuemsft_1-1711334894746.png

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])
)
)
)

vkaiyuemsft_2-1711334938123.png

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
Helper I
Helper I

Hello,

I think that DISTINCTCOUNT should do the work. 

QuentinBl_0-1711130868593.png

 



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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors