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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III
Helper III

Count instance of string in column

I have a table (L&L) as below which is connected to my date table:


Date           Job role codes concatenated

01/11/22      123b4, 34b56, 2b343, 3a421

03/11/22       123b4, 34b56, 2b343, 3a421

3/10/22         123b4, 34b56, 2b343, 3a421


I have another table containing the codes and definitions:

123b4 Vet

34b56  nurse

3a421 manager



I want to end up with a pie chart or table showing:


Job role

Vet        3

Nurse      2

Manager       4


How do I relate these two tables and make this happen please?

Super User
Super User

@gingerclaire You can either use Power Query Editor to split that column on commas and the unpivot or you could do this:

Measure = 
  VAR __Code = MAX('Table2'[Code])
  VAR __Table = FILTER('Table1',CONTAINSSTRING([Job role codes concatenated],__Code))
  VAR __Result = COUNTROWS(__Table)

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Thank you! I used this and it is working in a table BUT it is using COUNTROWS so is giving me the wrong totals - but i think i worked out why!

One a single row there might be several instances of the same string i.e. each row is an event and there might be 5 or six vets at that event. So it needs to count how often the string/code appears.

Is there a way to do this please?

Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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