Forum Discussion
RLS by Working Days
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.
I have attached my updated copy of your file to look at.
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.