RLS Data summarized and detail

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.