cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tonymaclaren
Helper I
Helper I

RLS problem with dynamic title for report

I have RLS implemented and it works perfectly. However I have a dynamic report title that works based on the following measure:

Selected Provider = IF(
NOT ISFILTERED('Providers'[Provider]),"All Providers",
SELECTEDVALUE('Providers'[Provider]))
This shows All Providers when RLS has no filters, but also shows All Providers when there are RLS filters applied, because as far as I can see RLS filters do not actually constitute a selection, and as a result everyone sees All Providers.
Providers are at the top level of a one to many model.
Any help would be much appreciated.

 

2 ACCEPTED SOLUTIONS

Hi there

What if you had to have the SELECTEDVALUE measure which would just use the default for example:

SELECTEDVALUE('Providers'[Provider],"All Providers")




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

Hi Gilbert

Thank you that worked. A good example of occam's razor!

The reason my code did not work is the use of NOT ISFILTERED. I had assumed wrongly that the PBI service engine would apply a filter by Provider. Not so. The reason your solution works is because as we know SELCETEDVALUE is just syntax sugar for:

IF ( HASONEVALUE ( Table[column] ), VALUES ( Table[column] ), "default value" )

So .

SELECTEDVALUE('Providers'[Provider],"All Providers")

 looks for one value, not to see if Provider is filtered.

Other replies to my post advising the use of USERPRINCIPLENAME() or USERNAME(), are wide of the mark because this approach only adds value if one wants to filter to an individual user. In my case I only need to filter RLS by an AAD security group, and the users within the relevant [Provider] AAD security group see only what RLS defines for that security group.

However, a Use case for a dynamic title for a user or all users would be SELECTEDVALUE([User],"All users")

where [User]. =USERPRINCIPALNAME() as long as the model is set up correctly.

Thanks again

Tony

View solution in original post

6 REPLIES 6
V-pazhen-msft
Community Support
Community Support

@tonymaclaren 


This post is not clear of what do you want to achieve. This is the correct measure when create a slicer with [Provider], you can switich providers name by selecting providers in the slicer. But any measures on the report has nothing to do with the RLS role setting.

 

If you want to show a "title" corresponds to each provider when providers open the report. This should be a case of Dynamic RLS, you can use Username() or Userprinciplename() to achieve this. 

https://community.powerbi.com/t5/Community-Blog/Dynamic-Row-Level-Security-Dynamic-RLS/ba-p/787399


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

GilbertQ
Super User
Super User

Hi there

It would depend on how you have configured your RLS.

If it is correctly applied I would expect the RLS to be applied no matter what the measure is being used?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Hi Gilbert

Thanks for getting back to me. I thought so too! RLS shows the correct content. Managemnet sees all the data (no Filters) and the title works dynamically and correctly. Providers see only their own data (filter by provider). It is only the title that does not work as I have described. As I have said it is because there is no default selection for no Provider. (.i.e. no filters are applied to the management Role.)

 

I can't see how to use Dynamic RLS in this situation because that would mean a many to many relationship of users sitting above Providers in the model. I would rather not use this approach because of the huge number of users.

Can IF then ELSE DAX be used in Role logic?

 

Thank you

 

Hi there

What if you had to have the SELECTEDVALUE measure which would just use the default for example:

SELECTEDVALUE('Providers'[Provider],"All Providers")




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Hi Gilbert

Thank you that worked. A good example of occam's razor!

The reason my code did not work is the use of NOT ISFILTERED. I had assumed wrongly that the PBI service engine would apply a filter by Provider. Not so. The reason your solution works is because as we know SELCETEDVALUE is just syntax sugar for:

IF ( HASONEVALUE ( Table[column] ), VALUES ( Table[column] ), "default value" )

So .

SELECTEDVALUE('Providers'[Provider],"All Providers")

 looks for one value, not to see if Provider is filtered.

Other replies to my post advising the use of USERPRINCIPLENAME() or USERNAME(), are wide of the mark because this approach only adds value if one wants to filter to an individual user. In my case I only need to filter RLS by an AAD security group, and the users within the relevant [Provider] AAD security group see only what RLS defines for that security group.

However, a Use case for a dynamic title for a user or all users would be SELECTEDVALUE([User],"All users")

where [User]. =USERPRINCIPALNAME() as long as the model is set up correctly.

Thanks again

Tony

That is awesome, thanks for letting us know that it does indeed work!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors