Forum Discussion

CR's avatar
CR
Resolver II
5 years ago
Solved

hide a sheet dynamically

Hi 

 

I got 2 sheets PUBLIC and PRIVATE in my report and PRIVATE should be accessible only for some users and according to a specific list of projects (2 conditions).

 

I've got 3 tables.

 

 

I also added a button with a dynamic text and a dynamic destination thanks to the following 2 measures:

Button text = IF(LOOKUPVALUE(Users[HideSheet],Users[Email],[UserPrincipaleName]) = 1,"GO TO PRIVATE","STAY ON PUBLIC")
Page Access = IF(LOOKUPVALUE(Users[HideSheet],Users[Email],[UserPrincipaleName]) = 1,"PRIVATE","PUBLIC")

It works well when I play with the roles for user1 and user2 but it is obviously not working for user3 as he can access to both projects (one is authorized to access the PRIVATE sheet but not the second one).

 

I have to add also another conditions to take into account the projectID because, so far, I deal with username only.

 

The PBIX file is available here. I hope this is enough to understand the case.

 

Thanks,

Camzo

Need Help
  • CR's avatar
    CR
    5 years ago

    Well, one of my colleague solved it by modifying the Button Text measure:

     

    Button text = 
VAR nbpjt = count(Users[ProjectId])
VAR typpjt = IF(SELECTEDVALUE(Users[HideSheet]) = 1,"GO TO PRIVATE","")
RETURN
IF(nbpjt > 1, typpjt, typpjt)

      

    Bye,

    CR

5 Replies

    • CR's avatar
      CR
      Resolver II
      5 years ago

      Hi amitchandak 

      I did the needful for RLS with a new User table and a role as follows:

      The project is correctly filtered but I still get the error for User3 because he can access to the 2 projects that get different values.

       

      Besides, even I select one project, the error in the button is not fixed.

      Does it mean that Button text measure should be more complex ?

      Button text = IF(LOOKUPVALUE(Users[HideSheet],Users[Email],[UserPrincipaleName]) = 1,"GO TO PRIVATE","STAY ON PUBLIC")

      Or is it still linked to the RLS ? I'm lost...

      New PBIX file is loaded.

      Thanks,

      CR

      • CR's avatar
        CR
        Resolver II
        5 years ago

        H everyone,

        I'm still stucked with that case.
        Here another graphical representation of the sample.

        I'm quite sure I have to add one requirement in the Button Text measure to add one condition through a variable, to mention that pre-selected project must be taken into account to know which message and action will be activated in the Button.

        • if pre-selected project gets 1 as value in HideSheet column, then display "GO TO PRIVATE" and gets the action "PRIVATE"
        • if pre-selected project gets 0 as value in HideSheet column, then display "STAY ON PUBLIC" and gets the action "PUBLIC"
        Button text = IF(LOOKUPVALUE(Users[HideSheet],Users[Email],[UserPrincipaleName]) = 1,"GO TO PRIVATE","STAY ON PUBLIC")
        Page Access = IF(LOOKUPVALUE(Users[HideSheet],Users[Email],[UserPrincipaleName]) = 1,"PRIVATE","PUBLIC")

        The only issue is I don't know how to proceed, and if something should be specify also in the role.

         

        Thanks for your support !

         

        Link to the PBIX file.

         

        Regards,

        CR

  • CR's avatar
    CR
    Resolver II
    5 years ago

    Hi,

     

    I just noticed the link to the PBIX file is not shared in my previous post.

    Here is the link.

     

    CR

  • CR's avatar
    CR
    Resolver II
    5 years ago

    Well, one of my colleague solved it by modifying the Button Text measure:

     

    Button text = 
VAR nbpjt = count(Users[ProjectId])
VAR typpjt = IF(SELECTEDVALUE(Users[HideSheet]) = 1,"GO TO PRIVATE","")
RETURN
IF(nbpjt > 1, typpjt, typpjt)

      

    Bye,

    CR