PBI Row Level Security and State-wide view

[IzakVA]

My app shows the state-wide data if a Ministry of Health (MOH) user is logged in and only the relevant Local Health District's (LHD) data if an LHD user is logged in. See the screen shots below.

MOH user: 

CCLHD user: 

Is it possible to let the CCLHD user see the state-wide view and then only allow the user to filter on their own LHD (CCLHD in this example). We want to allow the LHD user to view the state-wide view or the data for their own LHD, but not the data for any other LHD.

[Anonymous]

Hi @IzakVA ,

According to your description, here are my steps you can follow as a solution.

(1) We need to prepare a list of districts, which includes all districts.

User table:

Table: 

(2) New role.

var a=USERPRINCIPALNAME()
var b=LOOKUPVALUE(user[user type],user[user email],a)
var c=LOOKUPVALUE(user[District],user[user email],a)
var d=VALUES(user[District])
RETURN IF(b<>"MOH",[District]=c,[District] in d)

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[IzakVA]

Hi Neeko @Anonymous 

I have the list of districts in the LHD table. The LHD Name slicer is linked to this table. 

We don't reference a user directly in the RLS, since the list of users keeps on changing. I have a Role for each LHD which filters the LHD table for the relevant LHD.

Each Role is linked to a corresponding Active Directory group that contains all the users for that LHD.

[Anonymous]

Hi @IzakVA ,

It sounds like you have solved the problem yourself. Do you mean you want LHD users to view the statewide view? Then you could create a table with just one column of statewide regions and then create a table visual object. Note that this table has no model relationship to any other table and does not have any RLS set up. you should only see the CCLHD slicer and the statewide area reference table when you select it for the CCLHD role.
If I have misunderstood you, please clarify in a follow-up reply.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[IzakVA]

Hi Neeko @Anonymous 

Yes, I would like to have the LHD user view the state-wide data view and have only the Relevant LHD name available in the LHD slicer as a filter option. If the LHD is selected, only the LHD's data should be displayed. If unselected, the state-wide data should be displayed. Is this possible?

[Anonymous]

Hi @IzakVA ,

We need to prepare a separate table of state-wide data. There is no model relationship between this table and other tables.

Places the District column of the state-wide table on the table visual object.

New roles.

View as:

If anything is unclear to you, please refer to my pbix file.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[IzakVA]

Thanks for your quick reply. I don't think this will work for me, since I have a data model with many tables and 21 report pages with visuals that all need to work off the slicers. I can't duplicate the whole data model. Thanks for the suggestion.