September 27, 2018 at 5:13 pm #10676
On a worksheet, we can add a Slicer linked to a table in its Data Model. Can anyone suggest how we would use that Slicer? Can you point me to posts that could add more detail?September 27, 2018 at 6:52 pm #10677
Chapters 12 and 13 on Disconnected Tables/Disconnected Slicers from Rob Collie’s and Avi Singh’s “Power Pivot and Power BI”.
The basic strategy is that you create a measure in the data model to harvest the selection from the slicer on the disconnected table.
If you have some sample data, please attach.September 27, 2018 at 7:18 pm #10678
I saw those chapters, but I was trying to start at the ground level.
Logically, if a pivot is linked to a Data Model table, and a Slicer is linked to that table, then the Slicer should slice the pivot. But I couldn’t make that work in the simple test that I’ve attached.
I didn’t notice in those chapters the key step that would allow a Slicer to control pivots linked to Data Model tables.
Attachments:You must be logged in to view attached files.September 27, 2018 at 8:21 pm #10680
Thanks for the sample workbook, it helped me better understand your question.
Your slicer was not connected to the pivot. That connection should happen automatically if you right click on a field in the corresponding PivotTable Fields list.
If you select a cell in the pivot, the PivotTable tools context menu will appear in the Excel ribbon. On the Analyze sub-tab, go to the filter group and click on the Filter Connections button. This will launch the Filter Connections dialog, and you will notice that the box to the left of “Test” is unchecked. Check that box and you should be good to go.
Let me know if that helps.
Also, when testing a slicer, you will find will make more sense to add a measure to the Values drop zone of the pivot.September 27, 2018 at 8:35 pm #10681
Connecting to the pivot is what I’m trying to avoid, for three reasons.
First, as I asked at first, we can connect our slicer to a Data Model table, and I’m trying to discover what I can achieve by using that ability. I know, for example, that I can control CUBERANKEDMEMBER with such a slicer.
Second, in the workbook here, the Slicers are connected to the Data Model tables, but they DO control the pivot. However, so much is involved with that example, I haven’t yet boiled it down to the key method that allows it to control the pivot.
Third, if a Slicer can control a pivot through a Data Model table, that one Slicer should be able to control any pivot that’s linked to the table…which would be useful.
CharleySeptember 28, 2018 at 4:08 am #10682
In addition to CUBERANKEDMEMBER…
The CUBESET function can reference multiple slicer selections.
The CUBESETCOUNT function can count the number of slicer buttons selected.
The CUBEVALUE function can reference a slicer to manipulate the value of a measure.
Additionally, a slicer can manipulate pivot tables and pivot charts as long as the pivot chart or pivot table has a filter connection to that slicer.
However, by virtue only of its source is a data model field, a slicer can do little of any consequence.
For a slicer to affect a pivot table or pivot chart, a filter connection is required. And for CUBERANKEDMEMBER, CUBESET, CUBESETCOUNT, or CUBEVALUE, a reference to a slicer is required.
Perhaps I still do not understand your post, but the pivot table and pivot chart in the linked workbook have all filter selections checked. Please see the attached image.
Attachments:You must be logged in to view attached files.September 28, 2018 at 12:27 pm #10684
I’m late to this game. I’m trying to understand its rules. You’ve helped a lot. Thanks!
You must be logged in to reply to this topic.