cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
H_insight
Helper III
Helper III

RLS by Working Days

Hi all,

 

I have a model that consists of a sales and date table. I am trying to implement an RLS with the below criteria based on the following scenario:

“We are in March, and today is WD5, and I am still working and updating data for the Feb report. Reporting Month slicer is selected on Mar”

 

Normal users:

  • Can only see data in the report if we are on or after WD8.
  • If we are not on WD8, then they can only see data up to last month (Jan).
  • They can’t see any future data in the report (i.e. Mar & Apr).

Manager’s: No restrictions.

 

Any support is highly appreciated. Sample file.

 

Many thanks

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

Hello @H_insight 

You will need to create 2 roles, one for managers with no filters applied and one for normal users with a filter applied to the date table. 

The filter on the date table for the user role will be this.

VAR _Today = TODAY()
VAR _WD = LOOKUPVALUE ( 'Date'[WD Number], 'Date'[Date], _Today )
VAR _EOLM = DATE ( YEAR ( _Today ), MONTH ( _Today ), 1 ) -1
VAR _EOM = EOMONTH( _Today, 0 )
RETURN 
IF ( _WD < 5, 
'Date'[Date] <= _EOLM,
'Date'[Date] <= _EOM
)

Once you create the role you can go to modeling > view as and select the role to see the filter applied.  You can see that the table stops at march because the work day of TODAY() is >= 5 so it is showing the current month.

jdbuchanan71_0-1615475447898.png

I have attached my updated copy of your file to look at.

 

 

 

View solution in original post

5 REPLIES 5
jdbuchanan71
Super User
Super User

Hello @H_insight 

You will need to create 2 roles, one for managers with no filters applied and one for normal users with a filter applied to the date table. 

The filter on the date table for the user role will be this.

VAR _Today = TODAY()
VAR _WD = LOOKUPVALUE ( 'Date'[WD Number], 'Date'[Date], _Today )
VAR _EOLM = DATE ( YEAR ( _Today ), MONTH ( _Today ), 1 ) -1
VAR _EOM = EOMONTH( _Today, 0 )
RETURN 
IF ( _WD < 5, 
'Date'[Date] <= _EOLM,
'Date'[Date] <= _EOM
)

Once you create the role you can go to modeling > view as and select the role to see the filter applied.  You can see that the table stops at march because the work day of TODAY() is >= 5 so it is showing the current month.

jdbuchanan71_0-1615475447898.png

I have attached my updated copy of your file to look at.

 

 

 

Hi, Is there any way of adapting this to fit with my scenario?

 

So I have a report with different department areas which have different costs and profits which is summarised into one report for manager purposes. 

However, if I have RLS for each department is there a way to take the cost and profit from the general report and add/subtract a certain value from it? for example if the general says the average profit for all is 10 but for each department can I enter a dax measure at RLS to say cost which is 10 - 5 to make it accurate for each department?

Thank you very much @jdbuchanan71. Ths solution meets my execution for the Standard Users.


With regards to the Manager role, how can the report recognise them? do I need to use userprinciple ? or I need to add them to the dataset ->security-> add member?

You shouldn't need to mess with USERPINCIPLENAME because just being a member of the role applies the filtering you want.
You would create another role "Managers" that has no filters applied to it.
You would assign them to the role when you deploy the report.  If your active directory has a group like "All Manager" you can assign that group to the role.  If you don't have that you could work with your networkig team to make it if you are dealing with enough users for it to be a problem to keep up to date.

Load and clear! 🙂 Thanks for your help.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!