locked
How to lock one of the linked slicers for Pivot RRS feed

  • Question

  • I have two slicers, one for Hub, one for Country, both of them are connected with the same pivot table:

    eg:

    Hub slicer                        Country slicer

    Asia                                China

    Europe                            Japan

    Middle East                      Germany

                                          France

                                          Oman

    I have already created the hierarchy of them, so they are linked. It means if I select Asia, the Country slicer will pre-selected China and Japan.

    How can I lock the slicer for Hub, only "Asia" is allowed to select, but you still select between "China" and "Japan". I try a simple solution to lock the Hub slicer by protect sheet function with Lock. But when I go to Country slicer, if I choose France, even the Hub slicer is lock, it will still jump to "Europe".


                                          

    Wednesday, May 2, 2018 1:58 PM

Answers

  • it's more of a workaround but should give more or less what you need

    1) add this code to the VBA module (gets the current user name):

    Function GetUserName()
    GetUserName = Environ("username")
    End Function

    2) In Excel you create new table with users and respective regions

    Hub User Show
    Asia irene.zhang TRUE
    Europe other.user FALSE

    'Show' column is a formula with following syntax:

    =IF(GetUserName()=[@User],TRUE,FALSE)

    For this formula to work the VBA code needs to be in the file

    3) add the new table to PowerQuery, filter only rows that are TRUE, and remove all the columns other than Hub

    4) load the new table to the PowerPivot and create a join to your existing Hub table

    5) record a macro refreshing the new created table

    6) create a slicer based on the new table

    7) create a macro filtering out '(blanks)' from the slicer

    8) add a call of the recorded refresh macro (step 5) to the Workbook.Open event, as well as the filtering macro (step 7) 

    EDIT - table formatting
    Wednesday, May 2, 2018 4:08 PM

All replies

  • Wednesday, May 2, 2018 2:04 PM
  • Hi,

    Yes, I know it will works for Power BI. But my table is Pivot, so it does not have this function.

    Thanks.

    Wednesday, May 2, 2018 2:28 PM
  • do you mean pivot in Excel?
    if so you can create a second slicer on Hub column (in a hidden sheet), then hide items with no data in the slicer settings.
    Does that help?
    Wednesday, May 2, 2018 2:39 PM
  • Hi,

    Yes, pivot in excel.

    Actually, they all have data. The problem is like permission settings. We do not want people to select other Hub they do not belongs to, but we still want to give them freedom to select countries within the Hub. I can off course delete data from data model, but in this case I will have a lot of workbooks almost have the same structure.

    I try to find some VBA code to lock the slicer items. Or remove or delete items in the slicers. If it is possible to control it on item level.

    Wednesday, May 2, 2018 2:53 PM
  • is it more about the access rights or user experience?

    if it's about UI then VBA fix may help, but if you want to prevent people from accessing data in Excel then I don't think it's possible - at least not to my knowledge

    For UI - you could establish the correct hub based on user taken from:

    Environ("username")
    then you could have the slicer tables refreshed with relevant HUB only, and all others grouped into single item (blank)
    as I said users will always be able to just go to PowerPivot and look at the data if they want to
    Wednesday, May 2, 2018 3:08 PM
  • I have to say, it is not that kind of strict. If it is for accessing data, the most safest way is to remove data from model. If you just code in VBA, if someone has some experience, they will anyhow find it. 

    Could you show me how to write the code for refresh with the relevant HUB after you identify the Environ("Username")? When I am writing this VBA, I find for Sliceritems, it is only for ".Select" False or True. It is not the function I want.

    Wednesday, May 2, 2018 3:34 PM
  • it's more of a workaround but should give more or less what you need

    1) add this code to the VBA module (gets the current user name):

    Function GetUserName()
    GetUserName = Environ("username")
    End Function

    2) In Excel you create new table with users and respective regions

    Hub User Show
    Asia irene.zhang TRUE
    Europe other.user FALSE

    'Show' column is a formula with following syntax:

    =IF(GetUserName()=[@User],TRUE,FALSE)

    For this formula to work the VBA code needs to be in the file

    3) add the new table to PowerQuery, filter only rows that are TRUE, and remove all the columns other than Hub

    4) load the new table to the PowerPivot and create a join to your existing Hub table

    5) record a macro refreshing the new created table

    6) create a slicer based on the new table

    7) create a macro filtering out '(blanks)' from the slicer

    8) add a call of the recorded refresh macro (step 5) to the Workbook.Open event, as well as the filtering macro (step 7) 

    EDIT - table formatting
    Wednesday, May 2, 2018 4:08 PM
  • Hi Irene,

    Thanks for your question.

    As we know that Power Pivot does not support row level security. In your scenario, you might want to try to convert the Power Pivot Workbook to a Power BI Semantic Model or deploy the Power Pivot workbook to Share Point. See below blog talking about this similar issue:
    https://sqldusty.com/2015/08/25/taking-power-pivot-to-the-next-level/


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, May 3, 2018 3:22 AM
  • thanks
    Thursday, May 3, 2018 7:13 AM