RLS slices single select on

[mphillenga]

Dear community,

I have the following challenge:

I've built a report to represent values (e.g. sales, revenue) from a certain productcategory (category).
Also I've built a slicer to filter on these categories. The requirement is to set it to "single select on", because multiple categories are summed when shown together ("all selected") which presents wrong numbers.

In PowerBI service I'd like to give users access to the report per one or multiple categories. The issue is presented when I give a user access to multiple categories. When I set the slicer to "single select off" the RLS works fine. However when I set the slicer to "single select on" (which is what I want to achieve) the RLS doesn't work as it is preselected on a category when opening the report.

The "single select on" should be in place because if I give users access to more than one category, the data can not be summed.

Any idea how to solve this?

Thanks

[v-kkf-msft · Accepted solution]

Hi @mphillenga ,

This is the my test result, when I set "single select on", I can create a role that has permissions to multiple categories. What is the DAX formula using for your roles? Are you using dynamic RLS or static RLS?

[categories] = "C" || [categories] = "D" || [categories] = "A"

   

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[parry2k]

@mphillenga I know it is just not a great user experience but hope to improve one day. If you are interested, take a moment to subscribe to my youtube channel where I will be posting interesting Power BI related videos, link below. 

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[parry2k · Accepted solution]

@mphillenga it has nothing to do with RLS, unfortunately, this is how slicer works and there is no alternate to it. You can surely spend hours searching and thinking about it but at the end of the day this is how the product ( Power BI) works. Once we have the ability to set the default value of the slicers, it will help to solve this issue but don't know when that will come. There is an idea on the ideas forum for default value for slicers upvote for it.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[mphillenga]

Great thanks a lot for the clarity.

Not the answer I was hoping for, but very good to learn that this is the functionality for now and we should wait for Microsoft to change it.

Thanks a lot.

[v-kkf-msft]

Hi @mphillenga ,

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[v-kkf-msft · Accepted solution]

Hi @mphillenga ,

This is the my test result, when I set "single select on", I can create a role that has permissions to multiple categories. What is the DAX formula using for your roles? Are you using dynamic RLS or static RLS?

[categories] = "C" || [categories] = "D" || [categories] = "A"

   

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[mphillenga]

Dear Winniz,

Apologies for my late reply, due to the holiday period.

Thanks for your explanation, however it's not exactly helping the issue I have.

I'm working with static RLS. Let's say I have category A, B, C and D as in your example to determine RLS and a slicer in the report on category. The slicer is set to single select.

If I save the report in PowerBI desktop with the slicer on category C and publish, an user that has access to only category A will see in PowerBI service the slicer selected on category C (no data is shown, so that's working correct). The user then has to click on the slicer dropdown, will see category A besides the selected category C. If the user selects category A, category C will dissappear.

What I'd like to have is that when the user opens the report in the PowerBI workspace the slicer is automatically showing the category that that user has access to. Not the category that I saved the report in.

Is that possible?

Thanks

[parry2k]

@mphillenga check if this blog post helps that I wrote a long time ago How to set default value of single select slicer using RLS? | PeryTUS IT Solutions

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡