SLICER default settings in reports with RLS.

[ericjo]

I would like to set the SLICER default in the report with the RLS, but I would like to thank you for the function.

Name	Dept	Scope of Authority	Default Slicer
Eric	president	All	HR
Tom	HR	dl	HR
Josh	R&D	R&D	R&D
Stella	Sales	Sales	Sales

<RLS scenario>
1) If an ERIC with full privileges comes in, HR is selected and sliced.
2) Other people should be chosen by their own.

<Issue>
1) If DEPT is made by 'single selection' with HR for the entire permissions, HR is selected when the JOSH is connected to the report.
-> I want to make this part of R & D, not HR.

Thank you for telling you how to do it.

[v-kongfanf-msft]

Hi @ericjo ,

The solution provided by grazitti_sapna is feasible, can you provide relevant screenshot information as well as test data and describe it?

DefaultSlicerValue = 
VAR CurrentUser = USERNAME()
RETURN 
MAXX(
    FILTER(
        'UserTable', 
        'UserTable'[Name] = CurrentUser
    ), 
    'UserTable'[Default Slicer]
)

Best Regards,
Adamk Kong

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

[grazitti_sapna]

Hi @ericjo,
To handle default slicer settings in Power BI reports with RLS, you can use measures to dynamically set the default slicer values based on user roles. Here's how to achieve your goal:

1. Your data already includes a Default Slicer column. This column defines the default slicer value for each user based on their department.
2. Create a Measure to Dynamically Set the Default Value
   Use the following DAX measure to determine the default slicer value based on the logged-in user:
   

   • DefaultSlicerValue =
     VAR CurrentUser = USERNAME() -- Or USERPRINCIPALNAME() for email-based RLS
     RETURN
     MAXX(
     FILTER(
     'UserTable',
     'UserTable'[Name] = CurrentUser
     ),
     'UserTable'[Default Slicer]
     )

3. Add RLS Rules
   Implement RLS rules on the UserTable to ensure that users can only see their respective rows.
   For example:

   • Filter the UserTable so that each user can only see their own row.

   • Eric (President) should have access to all departments, while others are limited to their department.
     RLS filter example for the UserTable:

     • [Name] = USERNAME() || [Scope of Authority] = "All"

4. Set Up the Slicer

   • Add the Dept column to your slicer.

   • Use a sync slicer if you have multiple pages.

   • Enable Single Selection in the slicer settings.

5. Use a Bookmark for Default Slicer Behavior
   Create a bookmark to reset the slicer to the default value:

   • Set the slicer to the default value (e.g., HR for Eric).

   • Save this as a bookmark.

   • For users without Eric's access, the slicer will automatically be filtered by their department due to the

   • RLS and the DefaultSlicerValue measure.

Expected Behavior

• If Eric logs in, the slicer defaults to HR (as per the Default Slicer column). Eric can manually change the slicer value to view other departments since he has full access.
• If Josh logs in, the slicer defaults to R&D, and the report is filtered to show only R&D-related data.
• Other users like Tom or Stella will see their respective departments as the slicer's default and the report filtered accordingly.

Let me know if you need help setting this up!

If I have resolved your question, please consider marking my post as a solution. Thank you!
A kudos is always appreciated—it helps acknowledge the effort and keeps the community thriving.

[ericjo]

Thank you for informing me, and I tested the RLS based on the contents.

<Additional>
1. SLICER should be selected as DEAFULT settings.
-> If the report is posted on the basis of ERIC, the slicer is fixed to 'hr' when connected to Stella, and the figures are not available.
-> 'Sales' should be selected when connecting to Stella.

<Example IMG>