Forum Discussion

MrCoyado's avatar
MrCoyado
Frequent Visitor
9 years ago
Solved

Aggregated Measures with RLS

Hello everyone.   We have just implemented Power BI as our BI solution in my company (retail business). We are using RLS in our sales reports, which respect the company's hierarchy levels:   Sal...
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi MrCoyado,

     

    >>Is this possible with Power BI? We've tried using functions such as ALL and ALLSELECTED, but it didn't work with RLS.

     

    Based on test, I find that RLS is filtered on the dataset(original data will be filtered), so all and allselected function not work on RLS.

     

    In my opinion, you can calculate original data at query editor or database side and summary these records to a table. Then use the summary table to compare with current data.

     

    Regards,

    Xiaoxin Sheng

kaisrec's avatar
kaisrec
Advocate I
2 years ago

While RLS filters data at the row level, you can create measures that aggregate results at different hierarchy levels for comparison.

Here's a general approach you can follow:

  1. Create Aggregated Measures: Define measures that aggregate results at different hierarchy levels. For example:

    • Regional Sales Rep Sales: SalesRepSales = SUM('Sales'[SalesAmount])
    • Regional Manager Sales: ManagerSales = CALCULATE(SUM('Sales'[SalesAmount]), ALL('SalesRep'))
    • Director Sales: DirectorSales = CALCULATE(SUM('Sales'[SalesAmount]), ALL('RegionalManager'))

    These measures will disregard the RLS filtering and provide aggregated sales amounts.

  2. Create Comparison Measures: Calculate the percentage difference between the current level and the level above. For example:

    • Rep vs. Manager: RepVsManager = ([ManagerSales] - [SalesRepSales]) / [SalesRepSales]
    • Manager vs. Director: ManagerVsDirector = ([DirectorSales] - [ManagerSales]) / [ManagerSales]

  3. Implement RLS for Individual Level Security: Continue using RLS for individual-level security, ensuring that users at each level can only see their own data.

  4. Use Measures in Reports: In your Power BI reports, use the newly created measures for comparisons. The measures take into account the hierarchy levels and allow for comparing results without exposing individual results.

The use of ALL and ALLSELECTED functions is essential for creating measures that operate outside the filter context imposed by RLS.