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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.