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.
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.
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?
