SSAS RLS on specific dates, for PBI reports

[Anonymous]

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

[v-juanli-msft · Accepted solution]

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"))

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

Best Regards

Maggie

[Anonymous]

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")

- 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!