Forum Discussion

LeoQ's avatar
LeoQ
Icon for Helper I rankHelper I
5 years ago
Solved

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

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!

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    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.

8 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    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

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      LeoQ
      Icon for Helper I rankHelper I
      5 years ago

      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!

    • LeoQ's avatar
      LeoQ
      Icon for Helper I rankHelper I
      5 years ago

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

      Thanks!

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        5 years ago

        Hi,

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

  • LeoQ's avatar
    LeoQ
    Icon for Helper I rankHelper I
    5 years ago

    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's avatar
      v-deddai1-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      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