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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors