hide a sheet dynamically

[CR]

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

[CR · Accepted solution]

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

[CR]

Hi,

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

Here is the link.

CR

[amitchandak]

@CR , for page, better check for dynamic page Navigation with RLS.

https://www.youtube.com/watch?v=FrEDZZXiit8

For RLS -> can check if user and project join is bi-direction security join too ?

refer for step if needed -https://www.youtube.com/watch?v=MxU_FYSSnYU

[CR]

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]

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