topic Count instance of string in column in DAX Commands and Tips

Count instance of string in column

gingerclaire — Tue, 06 Dec 2022 16:52:56 GMT

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?

Re: Count instance of string in column

Greg_Deckler — Tue, 06 Dec 2022 17:05:06 GMT

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) RETURN __Result

Re: Count instance of string in column

gingerclaire — Wed, 07 Dec 2022 15:16:21 GMT

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?