Forum Discussion

Revati25's avatar
Revati25
Advocate I
3 months ago
Solved

RLS Issue

I have applied 2 different type of access in my dashboard
1) Full Region

2) Specific Clients 

In Specific Clients, When I am using RLS and Filtering to one email it is inflating my numbers
My calculations are:

VAR YearMonth =
    VALUES ( 'Date Table'[YYMM] )
VAR Numerator =
    CALCULATE (
        SUM ( 'Dovico Monthly - AGG Table'[WR <> Billable Hours (Formula)] ),
        TREATAS (
            VALUES ( 'Region Bridge'[Region] ),
            'Dovico Monthly - AGG Table'[Region]
        ),
        KEEPFILTERS ( 'Dovico Monthly - AGG Table'[YearM] IN YearMonth ),
        REMOVEFILTERS ('Dovico Monthly - AGG Table'[Business Line], 'Dovico Monthly - AGG Table'[ACIM_Solution])
    )
VAR Denominator =
    CALCULATE (
        SUM ( 'Dovico Monthly - AGG Table'[WR <> Billable Hours (Formula)] ),
        TREATAS (
            VALUES ( 'Region Bridge'[Region] ),
            'Dovico Monthly - AGG Table'[Region]
        ),
        KEEPFILTERS ( 'Dovico Monthly - AGG Table'[YearM] IN YearMonth ),
        REMOVEFILTERS ( 'Dovico Monthly - AGG Table'[Client_Name], 'Dovico Monthly - AGG Table'[Business Line],'Dovico Monthly - AGG Table'[ACIM_Solution]),
        REMOVEFILTERS('Client Bridge'[Client_Name]),
        REMOVEFILTERS('Client Level Access'[Client Name])
    )
RETURN
DIVIDE ( Numerator, Denominator )
 
My Model is:

 

My RLS Are:

On region bridge:

'Region Bridge'[Region] IN

CALCULATETABLE (

    VALUES ( 'Client Level Access'[Region] ),

    'Client Level Access'[Email] = USERPRINCIPALNAME ()

)

on client brige:

'Client Bridge'[Client_Name] IN

CALCULATETABLE (

    VALUES ( 'Client Level Access'[Client Name] ),

    'Client Level Access'[Email] = USERPRINCIPALNAME ()

)


on client level sheet:

'Client Level Access'[Email] ==  USERPRINCIPALNAME()

My client access sheet:

 


Clients Aonnet ID 
A           1
B            1
C             1

 

 

When i am applying 1) RLS for full region it is giving desired results
2) for Client level it is not giving correct numbers

Need Help
  • Revati25's avatar
    Revati25
    3 months ago

    Hi,


    Neither of the solutions worked out for me 

    Because

    1) RLS is applied before Remove Filters

    2) Due to this my table values are restricted according to the security level

    To solve this

    I created a seperate denominator summary table so RLS is not restricted on Client Level Role and my calculations are same as before.

     

    However Thanks for the help Ritaf1983  and DanieleUgoCopp 

5 Replies

  • DanieleUgoCopp's avatar
    DanieleUgoCopp
    Super User
    3 months ago

    Hello,

    I’m not completely sure, but this looks more like a filter propagation issue from the Client Bridge and the REMOVEFILTERS in your denominator. Since multiple clients share the same Aonnet ID, the RLS may be expanding the context and duplicating rows through the bridge relationships

    I would probably test first with simpler measures without REMOVEFILTERS, then check if the bridge tables have many-to-many relationships or bidirectional filters enabled. Also try validating the model by filtering a single client directly on the fact table to see if the inflation disappears.
    or you can make     simpler restructuring the access table into a single flat RLS table like:

    Email | Region | Client_Name

    then relate Region directly to the Region dimension and Client_Name directly to the Client dimension, avoiding many-to-many bridges if possible

    After that, your RLS can become much simpler on only the access table:

    'Client Level Access'[Email] = USERPRINCIPALNAME()

    and let the relationships propagate naturally through the model. Usually this avoids duplicated filters and inflated numbers, especially when multiple clients share the same ID

    Best regards,
    Daniele

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    3 months ago

    Hi Revati25 

    The issue you are experiencing occurs because DAX functions like REMOVEFILTERS cannot override Row-Level Security (RLS). RLS operates at the Storage Engine level, meaning the data is filtered before your DAX measure is even evaluated. When a user is restricted to a specific client, the underlying rows for other clients are physically inaccessible. Consequently, your Denominator measure attempts to remove the client filter to sum the entire region, but it can only evaluate the rows the user is permitted to see. This artificially shrinks the denominator to match the numerator, inflating your result. To resolve this, the recommended architectural approach is to create a pre-aggregated table at the Region level that does not include the Client dimension. You should apply RLS to this new table based only on the Region Bridge and point your Denominator calculation to it. This allows the measure to securely retrieve the correct regional total for the ratio without exposing restricted row-level client data.

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

  • v-prasare's avatar
    v-prasare
    Community Support
    3 months ago

    Hi Revati25,

    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.


    Ritaf1983 & DanieleUgoCopp ,Thanks for your prompt response

     

     

    Thank you for your patience and look forward to hearing from you.
    Best Regards,
    Prashanth Are
    MS Fabric community support

    • Revati25's avatar
      Revati25
      Advocate I
      3 months ago

      Hi,


      Neither of the solutions worked out for me 

      Because

      1) RLS is applied before Remove Filters

      2) Due to this my table values are restricted according to the security level

      To solve this

      I created a seperate denominator summary table so RLS is not restricted on Client Level Role and my calculations are same as before.

       

      However Thanks for the help Ritaf1983  and DanieleUgoCopp 

  • v-prasare's avatar
    v-prasare
    Community Support
    3 months ago

    Hi @Revati25,

    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