Forum Discussion
Dynamically generated date table using fiscal year start month in a multi-tenant dataset with RLS
- 6 years ago
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.
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
- Anonymous6 years agoNot applicable
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.
- Icey6 years ago
Community Support
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.