Forum Discussion

H_insight's avatar
H_insight
Helper V
5 years ago
Solved

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...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    5 years ago

    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.

     

     

     

jdbuchanan71's avatar
jdbuchanan71
Super User
5 years ago

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.

 

 

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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?