Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
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
v-juanli-msft
Community Support
Community Support

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
v-juanli-msft
Community Support
Community Support

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

Anonymous
Not applicable

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors