cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

SSAS RLS on specific dates, for PBI reports

Hi,

 

I'm developing PBI reports connected to SSAS Tabular 2016, and i'm using SSAS RLS, for now with 1 role (for 1 ADGroup AD_XXX) with 1 DAX Filter.
Now I need to add one DAX Filter that is different, based on data dimension.

 

The objective is that some users from AD_XXX will have access to all the dates, while others just to: 
Yesterday = TODAY() - 1
Last Day Of Last Month = EOMONTH(TODAY(), -1)
Last Day Of Last Year = DATE(YEAR(TODAY()) - 1, 12, 31)

 

This is dynamic, that is, for users with filters, for today 13/8, yesterday should be 12/8, but at Friday 17/8, "yesterday" will be 16/8.

For now i don't know how to distinguish the users, but for now i'll admit that some kind of 'User'[ProUserFlag] = 1 to have access to all, 0 only to those specific dates that change everyday (at least the Yesterday calculation)

 

How should i develop this requirement?

 

Regards

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: SSAS RLS on specific dates, for PBI reports

Hi @Anonymous

You could create "Yesterday",  "Last Day Of Last Month", "Last Day Of Last Year" as three measures, then create another measure to define if the "date" column equal to the row of three measures.

Look at my test with "Yesterday",  "Last Day Of Last Month", then create a measure to see which row od the date column equal to the measure.

Measure = IF(MAX([date])=[yesterday],"yeaterday",IF(MAX([date])=[Last Day Of Last Month],"Last Day Of Last Month"))

9.png

 

Then manage roles to limit data showing for this role. Then in Power BI Service, add the users whom you want to see only data of "Last Day Of Last Month".

10.png

11.png

 

Best Regards

Maggie

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: SSAS RLS on specific dates, for PBI reports

Hi @Anonymous

You could create "Yesterday",  "Last Day Of Last Month", "Last Day Of Last Year" as three measures, then create another measure to define if the "date" column equal to the row of three measures.

Look at my test with "Yesterday",  "Last Day Of Last Month", then create a measure to see which row od the date column equal to the measure.

Measure = IF(MAX([date])=[yesterday],"yeaterday",IF(MAX([date])=[Last Day Of Last Month],"Last Day Of Last Month"))

9.png

 

Then manage roles to limit data showing for this role. Then in Power BI Service, add the users whom you want to see only data of "Last Day Of Last Month".

10.png

11.png

 

Best Regards

Maggie

View solution in original post

Highlighted
Anonymous
Not applicable

Re: SSAS RLS on specific dates, for PBI reports

Hi @v-juanli-msft,

 

Thanks for your help :). Just some questions:

- "ou could create "Yesterday",  "Last Day Of Last Month", "Last Day Of Last Year" as three measures" -> you really mean measures, or calculated columns? I've created different 3 columns. And then another 3, because in the end the user can have access to 3 different days (ex. for today, yesterday is 15/8, Last Day of Last Month is 31/7, and Last Day of Last Year is 31/12/2017)

So, for example looking at "yesterday" level:

 

_Yesterday = TODAY() - 1

Yesterday =  IF(Dates[date]=Dates[_Ýesterday];"Yesterday")

 

temp.PNG

 

 

- Is this an efficient way to do it? About efficiency i could at least place it all under 3 columns.

Example for Yesterday:

Yesterday =  IF(Dates[date]=TODAY() - 1;"Yesterday")

 

- The one more nicer to the user could be to do something like this at Month and Year Level (because everyday has a "Yesterday")

Example for month: 

= IF(Dates[date]=Dates[_Last Day Of Previous Month] && Dates[_yesterday]=BLANK() ;"Last Day of Previous Month")

Just to avoid, in the limit at 01/01/20xx, to have 3 options that point to the same day 31/12/(20xx-1)

 

Regards!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors