Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
H_insight
Helper V
Helper V

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.

 

 

 

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.