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)

 

[VBA Geek]

Does this do what you want?

=<br><span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">COUNTROWS</span><span class="Parenthesis" style="color:#969696"> (</span> Projects <span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">FILTER</span><span class="Parenthesis" style="color:#969696"> (</span> Projects, <span class="Keyword" style="color:#0070FF">SEARCH</span><span class="Parenthesis" style="color:#969696"> (</span> <span class="StringLiteral" style="color:#D93124">"Paul"</span>, Projects[TeamMembers], <span class="Number" style="color:#EE7F18">1</span>, <span class="Number" style="color:#EE7F18">0</span> <span class="Parenthesis" style="color:#969696">)</span> > <span class="Number" style="color:#EE7F18">0</span> <span class="Parenthesis" style="color:#969696">)</span><br><span class="Parenthesis" style="color:#969696">)</span><br>

 

[ShaunP]

Does this do what you want?

=
CALCULATE (
COUNTROWS ( Projects ),
FILTER ( Projects, SEARCH ( "Paul", Projects[TeamMembers], 1, 0 ) > 0 )
)

Thank you VBA Geek for the suggestion! I copied your formula into the PowerPivot and received the error message below.

"Too Many arguments were passed to the "Search" Function. The maximum argument count for the function is 3.

 

[ShaunP]

I removed the ",0" from the Search function and received this error message.

"An Argument of function SEARCH has the wrong data type or has an invalid value. The column in the Projects table has a format of "TEXT" and the calculated column using this equation is Format General.

 

[ShaunP]

Does this do what you want?

=
CALCULATE (
COUNTROWS ( Projects ),
FILTER ( Projects, SEARCH ( "Paul", Projects[TeamMembers], 1, 0 ) > 0 )
)

I should add that eventually "Paul" will be replaced with the Username column (Users[Username]) in case this makes a differences with the formula. I did try using your equation by removing "Paul" with the column name and was give the following error:

The search string provided to function 'SEARCH' could not be found in the given text.

 

[VBA Geek]

Which version of Excel Power Pivot are you using? On the 2016 version I am not getting this error. What are the available arguments when you type this function?

Also, if I got this right, you may not want to link your tables in your data model. You want to create a new pivot table, drop UserName in the Rows section and then add this measure:

=<br><span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">COUNTROWS</span><span class="Parenthesis" style="color:#969696"> (</span> Projects <span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">FILTER</span><span class="Parenthesis" style="color:#969696"> (</span><br>        Projects,<br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">SEARCH</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent8">        </span><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">IF</span><span class="Parenthesis" style="color:#969696"> (</span> <span class="Keyword" style="color:#0070FF">HASONEVALUE</span><span class="Parenthesis" style="color:#969696"> (</span> UserNames[User Name] <span class="Parenthesis" style="color:#969696">)</span>, <span class="Keyword" style="color:#0070FF">VALUES</span><span class="Parenthesis" style="color:#969696"> (</span> UserNames[User Name] <span class="Parenthesis" style="color:#969696">)</span> <span class="Parenthesis" style="color:#969696">)</span>,<br>            [TeamMembers],<br><span class="indent8">        </span><span class="indent4">    </span><span class="Number" style="color:#EE7F18">1</span>,<br><span class="indent8">        </span><span class="indent4">    </span><span class="Number" style="color:#EE7F18">0</span><br><span class="indent8">        </span><span class="Parenthesis" style="color:#969696">)</span><br>            > <span class="Number" style="color:#EE7F18">0</span><br><span class="indent4">    </span><span class="Parenthesis" style="color:#969696">)</span><br><span class="Parenthesis" style="color:#969696">)</span><br>

 

[citizenbh]

The previous formula (VBA Geek) works for the Projects table.

Create a column in the Users Table:

=
CALCULATE (
    COUNTROWS ( Projects ),
    FILTER ( Projects, SEARCH ( Users[User Name], Projects[TeamMembers], 1, 0 ) > 0 )
)

VBA Geek: Sorry I did not see your answer

 

[ShaunP]

I'm using Excel 2010 and PowerPivot version 10.50.4000.0. The options available for the SEARCH function is only [start_num]. I would upload a screenshot but I'm not quite sure how as the insert image function wants a URL. My version also does not know what "HASONEVALUE" is.

Maybe my setup for all of this is incorrect? I am doing the calculations in the PowerPivot Window in the Users Table. My thought is that I run the calcs in here and then create a Pivot Chart & Table.

 

[stbrooks13]

I have a similar question - but I'm trying to create a measure for this as opposed to a calc column. I have one CANDIDATES table that shows all of my offers accepted and another INTERVIEWS table showing the source of all of the offers accepted. The linking ID is a column called the "system ID". I tried creating a measure as follows (for all intern offers accepted by attendees of our Explore conference), but it's blank. Any suggestions on how to create this measure without creating a calculated column?

CALCULATE([Interns],FILTER(Interviews,Interviews[System ID] IN DISTINCT(Candidates[System ID])),FILTER(Interviews,Interviews[Event]="EXP"),FILTER(Interviews,Interviews[Attendee Status]="Attendee"))

*Where* [Interns] = CALCULATE(COUNTA(Candidates[Offer Status]),OR(Candidates[Offer Status]="Offer Accepted",Candidates[Offer Status]="Hired"),Candidates[Job Level]="Intern")

 

[VBA Geek]

I'm using Excel 2010 and PowerPivot version 10.50.4000.0. The options available for the SEARCH function is only [start_num]. I would upload a screenshot but I'm not quite sure how as the insert image function wants a URL. My version also does not know what "HASONEVALUE" is.

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