Dynamically generated date table using fiscal year start month in a multi-tenant dataset with RLS

[Anonymous]

I am looking for any guidance that the community can provide.

I have a multi-tenant dataset which contains transactional data. The goal is to be able to present the data so that it is aligned to the customers' fiscal year. However, for each of the tenants , there could be a different fiscal year start month. Some are July, some October etc. I am also using row level security to ensure that each user has access to their data only for the transactional data. I also have a table that stores the customer profile (generic account name, not username) as well as their fiscal year start month. This is also filtered by RLS.

The problem I am facing is that I want to dynamically generate a date dimension table using the fiscal year start month from the customer profile table so that each customer sees their data aligned to their fiscal year. 

In my DAX for the date table I am trying to set a variable using a function that retrieves fiscal year start month from the account profile table. When I do a simple lookup I get a list of values when there should only be one (as filtered by row level security). I guess I would have expected that RLS would filter the data from the profile table and return a single value which I could use to generate the date table. Instead it seems as though it looks at the all of the profile data prior to RLS and hands me a (potentially incorrect) result.

Does anyone have any guidance in building a custom table based on a value that is filtered by row level security?

Thanks, any help is appreciated.

[Icey]

Hi @Anonymous ,

In my DAX for the date table I am trying to set a variable using a function that retrieves fiscal year start month from the account profile table. When I do a simple lookup I get a list of values when there should only be one (as filtered by row level security). I guess I would have expected that RLS would filter the data from the profile table and return a single value which I could use to generate the date table. Instead it seems as though it looks at the all of the profile data prior to RLS and hands me a (potentially incorrect) result.

How do you create it?

Best Regards,

Icey

[Anonymous]

Hi @Icey 

I have a table that contains profile data (1 row for each customer) with things like name, id etc. Additionally, I have a column for fiscal year start month. There are hundreds of rows of data and each represents a customer in multi-tenant fact tables (the profile table is also used for row level security by associated a customer id in that table with customer id in the fact tables). There is a single row per customer in the profile table.

In my DAX formula for the date table, I am setting a variable similar to: 

VAR fiscalstartmonth = profile[fy_startmonth]

I am expecting that only a single value should be returned since RLS should be limiting access to the data to a single row in the profile table. However, what I am seeing is that a list of values is being returned. The list is all the values for all the customers. A quick test using MAX(profile[fy_startmonth]) returns the highest value in the table (which is assigned to a different customer).

I was hoping that calculated tables were generated after the data was filtered by RLS. But it seems as though they are generated and then RLS is applied after the fact.

[Icey · Accepted solution]

Hi @Anonymous ,

Based on my test, it is not available in Power BI. Your calculated fiscal table can't be changed by RLS.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.