How do I get slicers to work with a pivot table with multiple value tables, and also displayed in an easily legible way?
Our take
I have an example list of fifty staff names with six different types of information for each staff member. Three of those criteria (Role, Types of Exp., and Clients) have multiple values, e.g. a staff member will have worked with multiple clients, have many types of experience, and have played different roles. My goal is to create a table of the staff that can be easily filtered according to any of the six criteria, including being able to filter by multiple values for a single criteria, e.g. search for staff members who are in the Chicago office with a bill rate of 200 or less, and who have worked with Northwind and Contoso.
I thought that creating a pivot table with slicers for each criterion would be a good way to do this. Steps I followed:
- I took my initial set of data and converted it to a table.
- I then created normalized data by copying the name column and each of the multiple value columns into three new sheets, and then used From/Table Range to access Power Query and transform the data into rows as a table.
- I then established relationships between each individual multiple value criteria table and the original staff table, using the Name column since it is what they have in common.
- I then created a pivot table from the data model, and added slicers for all six criteria based on the tables where those criteria live.
I'm stuck at this point. With just the Name field from the Staff table in the Rows Area of the pivot table, only the Office, Yrs. of Exp. and Bill Rate filters work correctly. If I want to get any of the multiple value filters to work, I need to add the slicer based on the table for that specific criteria, and then also add the corresponding field to the pivot table from the corresponding multi value table. Unfortunately, then the information is very difficult to read due to the way the pivot table displays and nests the information. A list of 50 names turns into over a thousand rows of data.
My questions are:
- Is there a way to get all six slicers to work with just the name field from the Staff table in the Rows area?
- Is there a way to also have the other criteria displayed in the pivot table in a more legible way, similar to the actual Staff table?
- If a pivot table and slicers are not the best way of going about this, what would be a better way? Ideally, I'd have the Staff table itself filterable with the slicers, but I'm not sure that's possible.
I am new to all of this and apologize if I am sometimes not using the correct terms. Thank you!
Edit: I am using Microsoft 365 on desktop.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience