Master Slicer to Filter 3 Columns

[powerpivotlegal]

Hello,

I have a database with the following Column Headers:
- Client ID
- Client Name
- Partner ID
- Partner Name
- Primary Team Member ID
- Primary Team Member Name
- Secondary Team Member ID
- Secondary Team Member Name

Each row will have unique values for each column (i.e. a person cannot be in both Partner and Primary Team Member columns).

I need a Master Slicer that looks through the Partner Name, Primary Team Member Name, and Secondary Team Member Name columns and displays all rows/clients that person is assigned to.

Currently, my nested IF formula in PowerPivot looks at all 3 columns, but isn't returning all the correct results.

For example, Client A has John Smith assigned as Partner and Bob Jones assigned as Primary. Client B has Bob Jones assigned as Partner and John Smith as Primary. If I select "Bob Jones" in my master slicer, it is only returning Client B rather than both rows Client A and Client B.

Can PowerPivot do this or only VBA?


Cheers!

 

[Matt Allington]

The detail is not 100% clear but my guess is the last 6 columns are all your staff. Assuming so, i would delete all ID columns (unless you need them for something else), then unpivot the 3 staff columns so you have.

Client, Staff Type, Staff Name
abc, primary, jimmy
abd, secondary, John

Etc

then you can filter by anything you want

 

[powerpivotlegal]

Thanks Matt. Are you saying that each client would then have 3 rows?

Client, Staff Type, Staff Name
Abc, inc., Partner, Jimmy
Abc, inc., Primary, John
Abc, inc., Secondary, Dave

I also have a "Notes" Column associated with each client. Would that data need to be copied into each new row? How do you easily transpose all of the Staff Type data into these new rows?

 

[Matt Allington]

If you have other data, i suggest put that into another table and join the one above and the new one to common lookup tables. Read my article here about multiple data tables. Multiple Data Tables in Power Pivot - Excelerator BI

You should used power query to shape your data. I have a video on my YouTube channel demonstrating how to unpivot.

I am developing power query training (still wip) but I have a free intro video that gives a broad overview of capabilities if you are interested. Http://xbi.com.au/pqt

 

[powerpivotlegal]

It doesn't matter if I have a lookup table for the ID or name and join it with master database in PowerPivot, my master slicer will not pick up the values in the other columns. Similarly, if I unpivot the data in Power Query and slice only on the one column with all unpivoted names, the slicer eliminates all the other team members for that client.

I need the slicer to return all the data on the row (Client Data and all team member data) when I select any team member's name. So, if I want to see all projects Joe is working on, I want the pivot to return Clients A, B, C, and E along with all the associated team members.
[TABLE="width: 806"]
<tbody>[TR]
[TD]SAP Raw Data Client Number[/TD]
[TD]SAP Raw Data Client Name[/TD]
[TD]Client Care Assigned Partner Name[/TD]
[TD]Client Care Primary Assigned Team Member Name[/TD]
[TD]Client Care Secondary Assigned Team Member Name[/TD]
[/TR]
[TR]
[TD]10069390[/TD]
[TD]Client A[/TD]
[TD]Joe[/TD]
[TD]Phil [/TD]
[TD]Karen [/TD]
[/TR]
[TR]
[TD]10081086[/TD]
[TD]Client B[/TD]
[TD]Bob[/TD]
[TD]Joe[/TD]
[TD]Phil[/TD]
[/TR]
[TR]
[TD]10002875[/TD]
[TD]Client C[/TD]
[TD]Joe[/TD]
[TD]Bob[/TD]
[TD]Karen[/TD]
[/TR]
[TR]
[TD]10000864[/TD]
[TD]Client D[/TD]
[TD]Bob[/TD]
[TD]Karen[/TD]
[TD]Phil[/TD]
[/TR]
[TR]
[TD]10002081[/TD]
[TD]Client E[/TD]
[TD]Joe[/TD]
[TD]Phil[/TD]
[TD]Karen[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]