cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MSW
Helper I
Helper I

Counting Text occurrences per Unique ID

Hello. I am trying to figure out how to make a measure to count the number of times a specific text appears in a column based on a Unique ID

 

Would look like this: 

IDText
2Apple
3Bannana
1Apple
2Bannana
3Kiwi
2Bannana
3Apple
1Bannana

 

When I wanted to count say "Bannna' it would give me 3 -- it would not count banana twice for the same ID. Apple would give me 3 and Kiwi would give me 1. 

2 ACCEPTED SOLUTIONS
lazurens2
Frequent Visitor

I am getting 3 for Bannana with this DAX Measure : 

CountTextById = Calculate ( 
    COUNTROWS ( VALUES ( 'Fruits'[ID] ) ),
    FILTER(ALL('Fruits'), 'Fruits'[Text] = "Bannana")
)

Tell me if it works for you.

View solution in original post

@MSW 

Yes but if you select a [Text] from the slicer it will give you the right number. Similirly if you place [Text] along with this measure in a table visual it should give the correct count for each text. If you are interested in summing these counts you can use

=
SUMX (
    VALUES ( 'Table'[Text] ),
    COUNTROWS ( CALCULATETABLE ( VALUES ( 'Table'[ID] ) ) )
)

This will give you 3 for banana, 3 for apple, 1 for kiwi and 7 for total. The total will change depending on the selected [Text] values. For example if you select "banana" and "kiwi" the total would be 4. You'll get the same number when using a card visual.

View solution in original post

4 REPLIES 4
tamerj1
Super User
Super User

Hi @MSW 

you may try

DISTINCTCOUNT ( 'Table'[ID] )

or

COUNTROWS ( VALUES ( 'Table'[ID] ) )

That doesn't give me the count based on the text criteria. That is giving me the total unique IDs of 3. I want to look at how many times a text criteria exists and only count it once per ID in the table.

@MSW 

Yes but if you select a [Text] from the slicer it will give you the right number. Similirly if you place [Text] along with this measure in a table visual it should give the correct count for each text. If you are interested in summing these counts you can use

=
SUMX (
    VALUES ( 'Table'[Text] ),
    COUNTROWS ( CALCULATETABLE ( VALUES ( 'Table'[ID] ) ) )
)

This will give you 3 for banana, 3 for apple, 1 for kiwi and 7 for total. The total will change depending on the selected [Text] values. For example if you select "banana" and "kiwi" the total would be 4. You'll get the same number when using a card visual.

lazurens2
Frequent Visitor

I am getting 3 for Bannana with this DAX Measure : 

CountTextById = Calculate ( 
    COUNTROWS ( VALUES ( 'Fruits'[ID] ) ),
    FILTER(ALL('Fruits'), 'Fruits'[Text] = "Bannana")
)

Tell me if it works for you.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors