RLS problem with dynamic title for report

[tonymaclaren]

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.

[GilbertQ · Accepted solution]

Hi there

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

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

[V-pazhen-msft]

@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]

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?

[tonymaclaren]

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

[GilbertQ · Accepted solution]

Hi there

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

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

[tonymaclaren · Accepted solution]

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

[GilbertQ]

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