Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Manager’s: No restrictions.
Any support is highly appreciated. Sample file.
Many thanks
Solved! Go to Solution.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |