Why am I a failure at relationships (in PowerPivot, not real life)?

[Bruce White]

I need some help with relationships in PowerPivot. While I have strong Excel 2010 and pivot table skills, the relationships in PowerPivot seem to be escaping me. I've read through a half-dozen tutorials on it and searched and read various forum posts. I think I know how it should work, but when I create the pivot table I think I want, it all goes PFUT!

I have 2 tables--Skills and Info (see below). Skills lists people and their skills. One combination of person and skill per row. A given person can have multiple skills. Multiple people can have the same skills. The Info table has one row per person. There is then a corresponding column with more information about that person.

Skills
[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Name
[/TD]
[TD]Skill
[/TD]
[/TR]
[TR]
[TD]Joe Swanson
[/TD]
[TD]Baseball
[/TD]
[/TR]
[TR]
[TD]Mary White
[/TD]
[TD]Volleyball
[/TD]
[/TR]
[TR]
[TD]Chua Vang
[/TD]
[TD]Soccer
[/TD]
[/TR]
[TR]
[TD]Billy Krueger
[/TD]
[TD]Bowling
[/TD]
[/TR]
[TR]
[TD]Mary White
[/TD]
[TD]Baseball
[/TD]
[/TR]
[TR]
[TD]Billy Krueger
[/TD]
[TD]Soccer
[/TD]
[/TR]
</TBODY>[/TABLE]


Info
[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Name
[/TD]
[TD]Information
[/TD]
[/TR]
[TR]
[TD]Joe Swanson
[/TD]
[TD]Went to school in Wisconsin; strong Excel skills
[/TD]
[/TR]
[TR]
[TD]Mary White
[/TD]
[TD]Graduated college 2000; works at IBM
[/TD]
[/TR]
[TR]
[TD]Chua Vang
[/TD]
[TD]Teaches ASL; married with 2 children
[/TD]
[/TR]
[TR]
[TD]Billy Krueger
[/TD]
[TD]Tried out for American Idol; spends too much time on Facebook
[/TD]
[/TR]
</TBODY>[/TABLE]

I created a relationship between the tables from Skills to Info using the Name column. Thus, Info was the "Related Lookup Table" and the Name column in Info was the "Related Lookup Column".

I wanted to create a pivot table with slicers such that if I chose a skill, only the people with that skill would be displayed, along with their corresponding informaiton. However, the pivot table wants to replicate every name with every skill and therefore repeating all the information multiple times.

Ultimately, my goal is to display to the user a unique list of skills (via a Pivot Table slice?). When a user clicks on a given skill, every person with that skill would be displayed on another clickable list. Clicking on that person's name results in the user seeing more information about that person (pulled from the Information column of the Info table). As a bonus display, when the person's name is clicked, I'd like to see what other skills they have.

For example, my list of skills would be: Baseball, Volleyball, Soccer and Bowling. When I click "Baseball", I would see Mary White and Joe Swanson. Then when I click Mary White, I would see "Graduated college 2000; works at IBM". Additionally, if possible, I would like to see that Mary has skills in Baseball and Volleyball.

Is PowerPivot the tool to do what I need?

Thanks in advance for any help!

Bruce

 

[MD610]

I don't know that PowerPivot can exactly replicate the functionality you are looking for based on your description.

However, it doesn't sound like you are using any measures. It sounds like you are just using fields in your rows and slicers. With a relationship, you normally need some type of measure that "ties" the 2 tables together for slicers to work properly on the pivot.

Try adding a simple measure to your pivot that counts the names in the skills table. Something like NameCnt:= COUNTA('Skills'[Name]). You can then just hide the column that holds that field in your pivot so you don't see it if you don't want to.

 

[Bruce White]

Re: Why am I a failure at relationships (in PowerPivot, not real life)? (SOLVED)

MD610--that was it! Not only does the table now behave as I think it should but between the table and the 2 slicers, I see all that I was hoping to see! With the judicious use of text boxes to display the results from my pivot table, I believe I have the results I was looking for!

Thank you!

Bruce

 

[TomHouy]

Re: Why am I a failure at relationships (in PowerPivot, not real life)? (SOLVED)

I just recently posted a thread on here about the same issue. Thank you for sharing the solution!

 

[subrosumon]

Hi, just cop & past sheet 1 to Sheet 2, i think it is Culmn A & B, A is Name & B is skill in sheet 2. now you should copy only name column from info sheet and pest to Sheet 2 end of 1st data (may be A6, than incert a new column C, and past from info to sheet 2 with column C6. and try to pivot

 

[RoryA]

@subrosumon

This forum is for questions about PowerPivot specifically, not regular pivot tables, so any answers should be PowerPivot specific.

Thanks.