Need Urgent help-RLS, A measure is resulting correct output when slicer values selected but not RLS

[maheshbabu]

Hi everyone, I am working on a finance report, where we deployed RLS to see their respective markets/verticals data. 

Context:

The Power BI report contains financial data, and certain measures such as Revenue_R and Unallocated need to be calculated without being affected by Row-Level Security (RLS). The challenge arises when attempting to bypass the RLS filter context to obtain correct values across different market segments.

Data Model:

Tables Involved:

1. FSL P&L Dimension (Dimension Table)

   • Contains Market 1, which is used for filtering.

   • Used to apply RLS.

2. FSL_P&L(Fact Table)

   • Stores actual revenue and unallocated amounts.

   • Contains PL Revenue, which is used in Revenue_R measure
     

     RLS Configuration:

     • RLS is applied on FSL P&L Dimension[Market 1], restricting users to see only the data relevant to their assigned market.

     • When RLS is applied, it limits the values that VALUES('FSL P&L Dimension'[Market 1]) can return.

     Measures : 
     Unallocated cost_Adj = SWITCH(TRUE(),

         HASONEFILTER('FSL P&L Dimension'[Market 1]) || HASONEFILTER('FSL P&L Dimension'[Verticals]),

         DIVIDE([unallocated_adj], [Revenue_R], 0) * [PL Revenue],

         SUMX(

             VALUES('FSL P&L Dimension'[Verticals]),

             SUMX(

                 VALUES('FSL P&L Dimension'[Market 1]),

                 DIVIDE([unallocated_adj], [Revenue_R], 0) * [PL Revenue])))

     unallocated_adj = CALCULATE(

         [Unallocated],

         ALL('FSL P&L Dimension'[Market 1], 'FSL P&L Dimension'[Capability 1]))

     Unallocated = CALCULATE([PL$],Mapping[PNL Group 2]="Unallocated")

     PL$ = CALCULATE(sum('FSL_P&L'[Amount in $]))/10^6

     Revenue_R =

     CALCULATE(

         [PL Revenue],

         SUMMARIZE(ALL('FSL P&L Dimension'), 'FSL P&L Dimension'[Market 1])

     PL Revenue = CALCULATE([PL$],Mapping[PNL Group 1]="Revenue")

     The desired output of the Unallocated cost_Adj measure is 0.8 for CMT and Diverse markets together, as both markets are considered into one RLS
     

     When selecting CMT and Diverse from Market 1 slicer, getting correct values like below

     

     You can see, I placed Market 1 in rows and verticals in columns, Unallocated cost_Adj for CMT market is 0.4+0.3 in two verticals(CMT and Collections), for diverse its is 0.1 in collections vertical, together getting 0.79 or 0.8 which is fine. But when RLS is applied on CMT below are the values

     

     Revenue_R and unallocated values are changing under RLS. Please guide me here feel free to ask me anything needed to under better

      

[V-yubandi-msft]

Hi @maheshbabu ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[V-yubandi-msft]

Hi @maheshbabu ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution we provided for your issue worked for you  or let us know if you need any further assistance?

Your feedback is important to us, Looking forward to your response. 

[V-yubandi-msft]

Hi @maheshbabu ,

Could you please review @SamanthaPuaXY ,  response and confirm if it meets your requirements? If you need any additional information or further clarification, feel free to let me know.

Also, if your issue is resolved with this solution, kindly mark it as accepted, it really helps others find the answer easily.

Thank you.

[SamanthaPuaXY]

Hi @maheshbabu ,

This is working as per RLS is designed.

To overcome the limitation you could create a separate table such as calculating Revenue_R and unallocated values on a separate table (if they are not meant to be affected by the RLS), then in the current measure, reference the new table for the values. Do not apply the RLS or have a relationship for the new table.

[maheshbabu]

Hello @SamanthaPuaXY , thanks for your response, would you help me in creating table?

[SamanthaPuaXY · Accepted solution]

Hi @maheshbabu 

This is roughly how your dimension table and fact table should look like and get linked

DimensionTb

FactTB:

Fact_no_RLS( A simple sum to get totalvalue and does not get impacted by RLS)

I create a measure with denominator not affected by the RLS

When viewed under RLS, it takes sum of product A (100) divide by total of 3 products (370)

Do give a kudos if it helped 🙂