April 25, 2017
The overall gist of my issue is as the title says: how to sync multiple slicers to pivot tables from different data sources. I saw a VBA solution by Bill Jelen that technically worked at first, but by adding some data to that tiny example, I managed to break how the slicers work! I'm wondering if the Data Model could offer a simpler and more robust solution to this issue.
Essentially, I've got a situation where I have 2 pivot tables (from different pivot table caches) on a single page. I'd like the slicer for the 1st pivot table to update the 1st pivot table, the 2nd pivot table AND a 2nd slicer (that displays other fields). And, I'd like the 2nd slicer for the 2nd pivot table to update the 2nd pivot table, the 1st pivot table AND the 1st slicer.
Since I have several more pivots, from several more data sources, and several more slicers to add to my 1-pager, it gets a little tedious to know which set of dual slicers one should show and which ones to hide. That last sentence makes more sense if you watch Bill's video in the link.
With my playing around with the data model so far, and from the MyOnLineTrainingHub classes, I know how to add a small helper table to link my two data tables (data sources), and then display a slicer connected to that helper table. That solution updates both pivot tables, but it doesn't update the 2nd slicer. So, that's why I'm asking for help on that issue.
Has anyone successfully tackled that one before? As an additional gotcha, I have a common data field between data tables 1 and 2, and a different, but common field between data tables 2 and 3. Bill said you have to have all tables have that common data field, so there might not be a solution to this...
July 16, 2010
It's not clear what this '2nd Slicer' is and its relationship to the data tables in the model. By default, Slicers will filter other Slicers where there is no data in that filtered state. e.g. let's say Slicer 1 contains Regions and Slicer 2 contains States. If you filter Slicer 1 on Region A, Slicer 2 will only display states that are within Region A.
There are various setting options in the Slicers that allow you to hide items with no data, show them last or show them greyed out. If none of those are what you want, then you probably need VBA to solve this. Without seeing a sample Excel file it's impossible to say whether this can be done with Power Pivot or not.
April 25, 2017
I documented what I wanted to do via an example. I've got 2 pivot tables and 2 slicers. If you filter via slicer #1, it updates both pivot tables as well as the slicer #2. If you filter slicer #2, it also updates both pivot tables, as well as slicer #1.
The example I provided has to do with allocations of people's time. I also included people salaries, so one could eventually use Power Query to perform some additional calculations of how much spending in incurred in particular divisions of a company, depending on how much of a person's time is spent working for that division. That part of the issue wasn't pertinent to this problem, so I excluded it from the example.
So, I have 2 questions.
1) Rather than craft a solution via macros, is there a better way to do this via Power Query and the data model?
2) This 2nd question may only make sense if you follow along in the example. I provided a 3rd list in the file although I didn't create a 3rd pivot table. If I did, you would see that the field for 'Person' is NOT part of this 3rd pivot table. Let's say I created the 3rd pivot able and also added a 3rd slicer (for Company) attached to the 3rd pivot table. Since 'Company' isn't part of the 2nd pivot table, I couldn't use the macro method to update the 1st and 2nd pivot tables. What I'd like to see happen is that I use the 3rd slicer to filter on Company One. This would only show Division A in the 3rd pivot table. Then, because only Division A is in the 3rd pivot table, I want to see the 2nd pivot table also show only Division A. That also corresponds to the Activity field of 'MFG & R&D' in the 2nd pivot table. Then, in the 1st pivot table, I'd like to see Bob and Jim listed because they work in the Activity of 'MFG & R&D'.
Commentary for Question #2. You may ask why I don't just create one big pivot table and then just connect all the desired slicers to that one table. I could do that, but I'm concerned that this example won't scale. Instead of 4 people, let's say there were 1,000. And instead of 1 or 2 Activities where people could charge their time to, let's say there were up to 20. You can see that the final pivot table would get big. It still might be small enough for a simple pivot table or a Power Pivot table to handle it, but I was hoping to develop a solution that didn't drag along every field from the original (1st) table to the subsequent tables.
Hopefully, that makes sense via email.
July 16, 2010
Thanks for the example. It looks like the 3rd table has an error because there are two rows for Division A and Company One, likewise for Division B and Company Two. The only difference being the percentages, which doesn't make sense to me.
Should I simply add the duplicate rows together so there is a one to one relationship between the divisions and companies?
April 25, 2017
Ah, sorry about that. I was playing around with that 3rd table, but forgot to change it back. What I meant to portray was that there was a 1:many relationship between Division and Company too. Please see the picture in the attachment for the correct row values.
In thinking about this problem further, I can envision the problem in one of several ways:
1. In addition to a slicer filtering a pivot table, it's a problem of that pivot table then filtering another slicer that is connected to a pivot table from a different data source. OR...
2. In VBA terms, it's a matter of a pivot table item updating a slicer item. Even if this could be done via VBA code, that sounds more like brain surgery to me! OR...
3. From a Data Model point of view, I know that one helper (unique) table can connect to 2 (many) tables and then you can use a slicer connected to that helper tables to drive what you see in pivots of the other 2 (many) tables. [The caveat there is that you have to include the field in the helper table in what you display in those separate pivot tables, per your teaching]. In that scenario, I believe the connection between the helper table and the other 2 tables would 1-way connections. What I suppose I am looking for is a 2-way connection between one of those (many) tables and another slicer (or helper table, I suppose) that is connected to a different data source. As someone who is no expert in using complex data models, I don't know if this is possible.
Although I've separately crafted a solution using "1 big table" with every data field in it, my concern, per my previous message, is that the solution won't scale. Additionally, I feel it goes against everything I learned about having a database architecture that is parsimonious i.e. not repeating data that is stored in another table.
Hopefully, all those analogies help you visualize what I'm trying to do with a more complicated hierarchy of data than just Person -> Activity -> Division -> Company.
July 16, 2010
Thanks for clarifying. I think you're taking the splitting of your data into separate tables too far. Ideally you'll have one fact table and then multiple dimension tables that take out the unnecessary duplicated data. I think it would be more productive if you showed me your "1 big table" and I'll tell you how you can structure your model, because the examples you've provided won't work they way you want.
April 25, 2017
I'm only a database novice, but I attached a few database details. I added them in with this copy of the original file to keep all this together. The pertinent tabs now are:
Schema with Separate Tables - the schema of what I consider to be the fact and dimension tables. This is too simplified though since it excludes all the calculated columns I add to the original fact table.
Schema with One Big Table - I think this is what you asked for. The "one big table" is located in columns AE-AO, although you can see the walkup of how I get there from starting in column A. In the top rows, I list just the tables that I would build up if I were constructing them via Power Query. In the lower rows is data that came from my original example, culminating with the example for the "one big table". I might have changed a data element or two, but the essence of it is intact. The green cells are data one would enter, and the grey cells are either calculated data or what I think of as foreign keys to the other tables.
Outputs - Since I think it is important to see what I'm trying to accomplish, I included pivot tables - all based on that one big table. This allows me to connect all the slicers to all the tables since all the PTs come from the same pivot cache. I had to coax the PTs to get them to do what I wanted, but I think it works. And although I think it does, I don't think this solution would scale if I had 500 people charging time to many categories for multiple divisions.
I hope the one big table, the walkup and the output pivots all make sense. Let me know if it doesn't. Thanks again for seeing if this solution can be improved upon.
July 16, 2010
Thanks for sharing the revised file. I can tell you that the 'one big table' isn't big at all, even if it has 100000's more rows than this sample.
At best you could remove the activity cost, division costs and company costs and calculate them with measures, but there isn't anything to move to a dimension table other than maybe the salary column. e.g. you'd have the following tables:
% of Person
% of Activity
% of Division
Remember, dimension tables must have a column containing a unique identifier. Taking your Activity Mapper dimension table, it doesn't have any columns without duplicates in it. The idea of dimension tables is to take data that's a subset of another field e.g. Person is the unique identifier and they might have other data associated with them like address, department, level, manager etc. That is the type of data you'd move to a dimension table. It's not the type of data you're trying to move to dimension tables.
In the attached file I've created an example model with some of the measures. I hope that points you in the right direction.
April 25, 2017
Thanks so much for providing an example file with the revised dimension and fact tables. Your response about the table structure made sense to me. And if I just keep the 'one big' data table as a connection and display the result as (a series) of pivot tables, I realize I won't have to ever list all the rows as output.
I still have to play around with the measures. The measures actually all need to add up to the same cost since it is the same 2 people whose salaries total up to $200 K in my example, no matter how much you slice it. I think I can do that by counting the number of visible rows in the filtered table and/or creating some keys (e.g. Name.Activity) and playing around with those as well.
The reason I used averages in my pivot table (which you commented on) was that I was trying to preserve certain values in the pivot table, regardless of some of the filters I was using. So, if i used the traditional 'sum' of Activity Cost, for example, of 2 rows of $40 K, I'd get $80 K, which is not what I wanted. By using an average, the table averages $40 K and $40 K, which is still $40 K. To total the value column in the pivot table, I was having to perform a sum outside the pivot. That method in itself is a squirly workaround to what I was trying to do. So, I'll need to work on that too.
What you did was to help me understand my problem better, as well as provide a framework for other similar challenges.