topic Counting Text occurrences per Unique ID in DAX Commands and Tips

Counting Text occurrences per Unique ID

MSW — Thu, 06 Oct 2022 17:30:28 GMT

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:

ID	Text
2	Apple
3	Bannana
1	Apple
2	Bannana
3	Kiwi
2	Bannana
3	Apple
1	Bannana

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.

Re: Counting Text occurrences per Unique ID

tamerj1 — Thu, 06 Oct 2022 17:38:08 GMT

Hi MSW

you may try

DISTINCTCOUNT ( 'Table'[ID] )

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

Re: Counting Text occurrences per Unique ID

MSW — Thu, 06 Oct 2022 21:32:53 GMT

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.

Re: Counting Text occurrences per Unique ID

lazurens2 — Thu, 06 Oct 2022 23:00:03 GMT

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.

Re: Counting Text occurrences per Unique ID

tamerj1 — Fri, 07 Oct 2022 01:00:27 GMT

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.