Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Row Level Security on Composite Model

Hello,   In my report, I have connected to SSAS cubes data, Power BI dataset and Sharepoint data. 1.Imported one table of suppliers with Supplier ID from SSAS cube and direct queried other tables ...
Need Help
ABI_Rufino's avatar
ABI_Rufino
Advocate I
3 years ago

I have the same exact problem.

 

My scenario is:

 

  • Power BI Premium Workspace;
  • Composite model with:
    • Power BI Dataset Direct Query (AS) - let's call it 'base dataset'
    • Excel file on Sharepoint folder;
      • Only 2 columns: user email and the region that i want to filter;
    • The excel file relationship is with the dimension as a many-to-many, one-directional (excel filters the dimension);
  • RLS rule is simple: [user_email] = USERPRINCIPALNAME(), so when user is logged to PBI Service the excel table is filtered and it SHOULD filter the dimensions table, and them filtering the fact table;

RLS works fine if i import the excel file to the 'base dataset'. But i don't want that because it's a shared dataset and RLS will apply only to a few reports, not all of them. So for those that will apply, i've created this composite model, but for some reason the RLS rules are not working (the excel table is being filtered, but the filter is not propagating).

  •  
marcecane's avatar
marcecane
Regular Visitor
1 year ago

Hello ABI_Rufino 
my use case is exactly like yours. Same steps but still no solution. Did you find a solution to propagate the filter to the direct query tables?
Thank you in advance for your eventual reply

 

  • ABI_Rufino's avatar
    ABI_Rufino
    Advocate I
    9 months ago
    marcecane wrote:

    Hello ABI_Rufino 
    my use case is exactly like yours. Same steps but still no solution. Did you find a solution to propagate the filter to the direct query tables?
    Thank you in advance for your eventual reply

     

    It's a limitation of the tool.

    Details here: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models#working-with-a-composite-model-based-on-a-semantic-model

     

    • RLS rules are applied on the source on which they're defined, but aren't applied to any other semantic models in the model. RLS defined in the report isn't applied to remote sources, and RLS set on remote sources aren't applied to other data sources. Also, you can't define RLS on a table loaded from a remote source, and RLS defined on local tables don't filter any tables loaded from a remote source.

     

    If you are not going to work with embedded like Anonymous, then you will need to bring those tables inside your main model, and then to a live connection from the report.

     

    We had a limitation because we wanted to refresh just a few set of tables, and that's why we've built the composite model. But what we did is to bring everything to this unified model and use Power BI REST API to refresh specific tables on the model only (also, on Fabric pipelines you can refresh a semantic model specific table).