Counting text values from 2 columns using aonther table as a criteria

[LeoQ]

Hi everybody, it's my first time I'm writting here because I always found solutions here, but his wasn't the case.

I am working with responses from a form and there's one column named 'Teams', where the same person may be part of different teams. For example, I can find "Mike - Eva" as well as "Eva - Mary".

As I'm trying to know how many times every person appears in the table, I divided the column into many others, getting different columns with only 1 name.

However, when I want to know how many times every name appears in the table (taking into account that 1 name may appear in different columns), I get stuck.

I created a new table with every name and it is related to every column I got when divided the main one. I tried to build a measure but I couldn't, specially beacuse the functions I used only return the first column and not the others.

I hope I'd been clear and sorry for this long message.

Thanks a lot!

[LeoQ]

Thanks for your answers! I'll give an example
I have 2 initial tables, TABLE A with people and TABLE B, with information about request they solved.

However, I divided the column team in TABLE B, so I got the TABLE C.

How can I know how many times each name appears in TABLE C? taking as reference the names on TABLE A (I have to manipulate this data in order to build some performance measures).

I'm sorry i had to send the tables as photo, but the editor showed me errors when I wrote the tables in here.
Thanks a lot!

[v-deddai1-msft]

Hi @LeoQ ,

You can unpivot your table C and then use the following measure:

Meaure = CALCULATE(COUNT(tableC[VALUE]),FILTER(tableC, tableC[VALUE] = MAX(tableA[name])))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Ashish_Mathur]

Hi,

Have you tried my suggestion? 

[Ashish_Mathur · Accepted solution]

Hi,

In the Query Editor, on the Split screen window, click on Advanced and select rows there.  This way, all names will appear in a single column.

[LeoQ]

Is there a way to make that task automatically? beacuse I have to work with real-time data.

Thanks!

[Ashish_Mathur]

Hi,

What do you mean by that?  The process I shared is a one time activity.

[LeoQ]

Sorry for the delay in my reply. Even though it generates tons of rows when I have 9 different values in 1 column, it really helped to make the count. Thanks a lot!

[mahoneypat]

Please share some example/mock data so a specific solution can be suggested.  If you've split your name strings into columns, you'll want to unpivot those colums for easier analysis with DAX.  However, you likely could keep the string intact and use SEARCH.

Pat