PowerPivot - Searching Between Tables

[ShaunP]

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
[TABLE="class: grid, width: 250, align: center"]
<tbody>[TR]
[TD]User Name[/TD]
[/TR]
[TR]
[TD]George [/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[/TR]
[TR]
[TD]Oscar[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[/TR]
</tbody>[/TABLE]


Projects Table

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Project[/TD]
[TD]Team Lead[/TD]
[TD]TeamMembers [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]George[/TD]
[TD]Paul; Mary; Peter[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Paul[/TD]
[TD]Oscar[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]George[/TD]
[TD]Mary; Paul[/TD]
[/TR]
</tbody>[/TABLE]

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.

=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

=if(ISERROR(search("Paul",Projects[TeamMembers],1)),1,0)

 

[ShaunP]

You are running a VERY old version of PowerPivot known as Power Pivot 2008 R2. Since you are on Excel 2010, you can try to update your PowerPivot version to the latest one supported on Excel 2010 at the following link: https://www.microsoft.com/en-US/download/details.aspx?id=43348

Thank you VBA Geek! Your formula now works after the upgrade!