locked
How do I populate a slicer dynamically from a selected record within a linked table in a different Excel work sheet RRS feed

  • Question

  • Hi,
    I have a Table A within Worksheet A which contains a number of records.
    Table A is also uploaded as a PowerPivot linked table.
    I have selected a particular record within Table A.

    I have a Table B within Worksheet B which contains a different set of records.
    There are several slicers for Table B.
    I want the slicers for Table B to be dynamically populated by the values in the selected records within Table A.
    So the slicers have a smaller more manageable sub set of values within Table B.

    So do I populate a slicer dynamically from a selected record within a linked table in a different Excel work sheet?

    I am aware that I can use the ALLSELECTED Function (DAX) https://msdn.microsoft.com/en-GB/library/gg492186.aspx.

    However I need to know how to dynamically change the data source of a slicer.

    I am currently looking at; - Slicers.Add Method (Excel)

    I am also looking at disconnected tables.

    Kind Regards,

    Kieran.


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/



    Monday, January 4, 2016 2:18 PM

Answers

  • Sorry it took me so long to update this thread.

    Basically an answer is to share filters among among pivot tables where  these pivot tables source the same PowerPivot model.

    This is highlighted in slide 27 of the following slide share; - 

    http://www.slideshare.net/KieranWood/contrasting-querying-data-in-a-tabular-format-within-power-bi-excel-2016-and-ssrs-2016

    This slide is titled; - "Pivot Tables: Share Filters to Provide Consistent User Experience".

    You can use the above approach to share multiple filters between different pivot tables. These filters could be for example Product Category, Product Sub Category and Product. So if you select a share filter value for Product Category the number of share filter values for Product Sub Category will become more manageable etc.


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/



    Saturday, July 30, 2016 9:11 PM

All replies

  • A slicer is always based on one column in the Power Pivot model. You cannot dynamically populate a slicer.

    What you're looking for may be done using the 'hide items with no data' option of a slicer. This means you should have the selection in table A causing the table B to only have results (in the pivot tables) for the records in table B that you want. This may work with a relationship between A and B, or by having an appropriate DAX statement as a measure. And obviously, B should be in Power Pivot as well.

    Without more information on your model I cannot go into more detail. I don't think ALLSELECTED will help you, though.

    Tuesday, January 5, 2016 7:31 AM
    Answerer
  • I am also looking at http://www.daxpatterns.com/parameter-table/

    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/

    Tuesday, January 5, 2016 11:17 AM
  • A parameter table is used to provide a number of items that a user can select from that will influence the execution of a measure. The value selected in the parameter table is treated as if it were a parameter, thus the name.

    You cannot utilize a parameter table to limit the values displayed in another slicer by any method which would be more powerful or convenient than introducing a hierarchy in the base table.

    GNet Group BI Consultant

    Tuesday, January 5, 2016 3:49 PM
  • Hi Kieran,

    how about using Power Query to filter Table B by slicer selection of Table A before loading to the Power Pivot model (Merge with JoinKind.Inner)?

    If the slicer selection isn't already available in one of the "report-pivots" you could create a hidden pivot that just delivers the values that you need. Advantage of harvesting a slicer selection in a different workbook is that you can retrieve it as a worksheet (no need to create a named range with an offset-function). For details see http://www.thebiccountant.com/2015/08/18/use-timeline-or-slicers-to-filter-your-power-query-import/

    Limitations:

    1) Changes in workbook containing Table A must be saved in order to be valid in Table B's workbook

    2) Canges in Table A require a refresh of Power Query


    Imke Feldmann TheBIccountant.com



    Thursday, January 7, 2016 5:46 AM
    Answerer
  • I believe I have completed this task. I am currently working very long hours so I will try and create a blog which contains the solution towards the end of next week. Saturday 16th January.

    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/

    Thursday, January 7, 2016 5:58 PM
  • I believe I have completed this task. I am currently working very long hours so I will try and create a blog which contains the solution towards the end of next week. Saturday 16th January.

    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/

    Hi Kieran,

    Glad to hear that you have completed the task. So could you please share the blog link on the thread when you finish the blog? It will help others who have the similar issue.

    Regards,


    Charlie Liao
    TechNet Community Support

    Friday, January 8, 2016 2:09 AM
  • Sorry still working on this blog entry. I will update this thread as soon as it is complete.

    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/

    Monday, January 18, 2016 6:46 PM
  • Sorry it took me so long to update this thread.

    Basically an answer is to share filters among among pivot tables where  these pivot tables source the same PowerPivot model.

    This is highlighted in slide 27 of the following slide share; - 

    http://www.slideshare.net/KieranWood/contrasting-querying-data-in-a-tabular-format-within-power-bi-excel-2016-and-ssrs-2016

    This slide is titled; - "Pivot Tables: Share Filters to Provide Consistent User Experience".

    You can use the above approach to share multiple filters between different pivot tables. These filters could be for example Product Category, Product Sub Category and Product. So if you select a share filter value for Product Category the number of share filter values for Product Sub Category will become more manageable etc.


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/



    Saturday, July 30, 2016 9:11 PM