Forum Discussion
Advocate VRLS Data summarized and detail
Hi community, in this time I going to development a RLS with detail data, but the RLS roles should be show data in a next level of RLS in each role, I mean:
Data dimensions are : Country, state, city. (revenues) -> First Rule
Role: Administrator: can see all data (World)
Role: Director: Can see one and two countrys (US, MX) and all states and cities.
Role Manager: can see one or two states (TX, NY, CDMX) and all cities.
The difference is in each role; the role can see one level up.
The requiered rule.
Example:
Director: US and MX and only the total of country of these states.
| Country | Revenues |
| US | 1,000,200,000 |
| MX | 1,000,100,000 |
| World | 10,000,000,000 |
Manager: states: TX and CDMX and the total from this
| Location | Revenue |
| TX | 1,000,000 |
| CDMX | 3,000,000 |
| US | 1,000,200,000 |
| MX | 1,000,100,000 |
So in the data is in level in city. So the measure is a SUM fuction, and group by dimension.
| Country | State | City | Revenue |
| US | TX | Houston | 300,000 |
| MX | CDMX | CDMX | 350,000 |
In fact, the RLS works great with the first rule, for to get the required rule. I actually use another query, to get the data in one level up, so my questions are:
Is there a RLS configuration to get the grand total but restriction the data to the user final?
Is a good option to apply OLS to secure columns in the new query?
another tip or trick to archive the required rule? Thanks in advance.
Hi Peter_23
to achieve this, you need to differentiate between Data Privacy (hiding rows) and Data Granularity (summarizing data).
First, a quick clarification: OLS (Object-Level Security) is not the solution here. OLS hides entire columns or tables from the metadata. If you apply OLS to a "Revenue" column for a Manager, their visuals will simply break with an error rather than showing a summarized total.
To show "Grand Totals" while restricting "Detail Data," I recommend the following strategies:
1. The Aggregation Table Approach (Recommended)
The most stable way to show a "World Total" to someone who only has "Country" access is to decouple the data into two tables:Fact_Detail: Contains City-level data. Apply your strict RLS here.
Fact_Summary: Contains only Country-level or World-level totals. Apply no RLS (or very loose RLS) here.
Implementation: In your report, the "Total" cards or high-level charts point to Fact_Summary, while the drill-down tables point to Fact_Detail.
2. Disconnected Tables for "Grand Totals"
If you want to keep one fact table, you can create a Disconnected Table (a table with no relationships) that contains the high-level attributes (Country/World).Since there is no relationship, the RLS filters applied to the main dimension tables will not propagate to this disconnected table.
You then use a DAX measure with LOOKUPVALUE or CALCULATE(SUM(...), ALL(Geography)) to pull the data.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
4 Replies
- Ritaf1983
Super User
Hi Peter_23
to achieve this, you need to differentiate between Data Privacy (hiding rows) and Data Granularity (summarizing data).
First, a quick clarification: OLS (Object-Level Security) is not the solution here. OLS hides entire columns or tables from the metadata. If you apply OLS to a "Revenue" column for a Manager, their visuals will simply break with an error rather than showing a summarized total.
To show "Grand Totals" while restricting "Detail Data," I recommend the following strategies:
1. The Aggregation Table Approach (Recommended)
The most stable way to show a "World Total" to someone who only has "Country" access is to decouple the data into two tables:Fact_Detail: Contains City-level data. Apply your strict RLS here.
Fact_Summary: Contains only Country-level or World-level totals. Apply no RLS (or very loose RLS) here.
Implementation: In your report, the "Total" cards or high-level charts point to Fact_Summary, while the drill-down tables point to Fact_Detail.
2. Disconnected Tables for "Grand Totals"
If you want to keep one fact table, you can create a Disconnected Table (a table with no relationships) that contains the high-level attributes (Country/World).Since there is no relationship, the RLS filters applied to the main dimension tables will not propagate to this disconnected table.
You then use a DAX measure with LOOKUPVALUE or CALCULATE(SUM(...), ALL(Geography)) to pull the data.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
v-prasareCommunity Support
Hi Peter_23,
Ritaf1983 ,Thanks for your prompt response
Duplicating the detail table to create a totals/summary table is a valid approach when custom calculations can’t be summarized in a single measure. For the second totals table, hiding columns using the model/format option is the correct choice. This only affects usability by keeping technical columns out of the Fields pane, while visuals and measures continue to work as expected.Object‑Level Security (OLS) is not recommended here because it removes columns from the model metadata, any visual or measure that depends on those columns will fail. OLS is meant for strict metadata security, not for simplifying summary tables. In this scenario, hiding columns is sufficient for end users, and security should be enforced through RLS on the detailed fact table. This aligns with Power BI/Fabric modeling best practices and avoids broken visuals.please refer this blog and let me know if it helps:
Power BI Security: A practical guide to RLS, OLS a... - Microsoft Fabric Community
Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support
Peter_23Thanks Ritaf1983 , the fact_summary is a great idea, but this time, one measure with custom calculation is not possible to summarized, so there isn't option than duplicate the table detail and get total from there.
I have a questions, so the second table (totals) are many columns shouldn't be access to the user final.
It is possible hide or OLS to these columns.
So, what are the diferent from hide (format option) column or use OLS to columns for the final user?
any option could be work for the user final?
anyone is it better for security?
thanks in advance.
- v-prasare
Hi Peter_23 ,
We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.
Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support