Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 diff...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    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

v-juanli-msft's avatar
v-juanli-msft
Community Support
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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!