Home Forums Power Pivot Data Model Slicer on a Worksheet?

This topic contains 6 replies, has 2 voices, and was last updated by  Charley 10 months, 4 weeks ago.

Viewing 7 posts - 1 through 7 (of 7 total)
  • Author
    Posts
  • #10676

    Charley
    Participant
    • Started: 10
    • Replies: 7
    • Total: 17

    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?

    #10677

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2566
    • Total: 2573

    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.

    #10678

    Charley
    Participant
    • Started: 10
    • Replies: 7
    • Total: 17

    Tom,

    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.

    Thanks.

    Charley

    Attachments:
    You must be logged in to view attached files.
    #10680

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2566
    • Total: 2573

    Charley,

    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.

    #10681

    Charley
    Participant
    • Started: 10
    • Replies: 7
    • Total: 17

    Tom,

    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.

    Charley

     

    #10682

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2566
    • Total: 2573

    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.
    #10684

    Charley
    Participant
    • Started: 10
    • Replies: 7
    • Total: 17

    Tom,

    I’m late to this game. I’m trying to understand its rules. You’ve helped a lot. Thanks!

    Charley

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic.