Hi All, I'm new to PowerPivot and running into an issue with searching between 2 tables. I setup 2 tables in PowerPivot and created a relationship between them. I am trying to count the number of instances a user shows up in a specific column. The column may contain several users within the cell. I have tried a few equations and I am not having any luck. I appreciate any help you may provide.
I created the following:
Users Table
<tbody>
</tbody>
Projects Table
<tbody>
</tbody>
I created a relationship between Users [User Name] and Projects [Team Lead]. I am able to count the number of instances a User shows up in the Team Lead column.
I tried the following code to count the rows in the Projects table that contain "Paul" in the Team Member List but the result is blank.
I used the following code just to see if I can find Paul in the Team Members column but I get an error stating column TeamMembers in table Project cannot be determined in the current context
I created the following:
Users Table
User Name |
George |
Paul |
Mary |
Oscar |
Peter |
<tbody>
</tbody>
Projects Table
Project | Team Lead | TeamMembers |
1 | George | Paul; Mary; Peter |
2 | Paul | Oscar |
3 | George | Mary; Paul |
<tbody>
</tbody>
I created a relationship between Users [User Name] and Projects [Team Lead]. I am able to count the number of instances a User shows up in the Team Lead column.
I tried the following code to count the rows in the Projects table that contain "Paul" in the Team Member List but the result is blank.
Code:
=CALCULATE(COUNTROWS(Projects),filter(Projects, Projects[TeamMembers]="Paul"))
I used the following code just to see if I can find Paul in the Team Members column but I get an error stating column TeamMembers in table Project cannot be determined in the current context
Code:
=if(ISERROR(search("Paul",Projects[TeamMembers],1)),1,0)