Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

RLS in Manage aggregation giving error.

Hi,  I've created manage aggregation model it's working fine and I tested on Dax studio, all quries are navigating to aggregated tables. Bur after creating role for RLS on one table, all visuals ar...
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi Anonymous ,

     

    Does your RLS meet the following condition?

    "To work correctly for aggregations, RLS expressions should filter both the aggregation table and the detail table."

     

    Best regards,
    Lionel Chen

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

  • Anonymous's avatar
    Anonymous
    6 years ago

    v-lionel-msft 

    mwegener 

     

    Found the problem.

    By misktake I've added report level filter from aggregated report after removing that filter RLS worrking fine.

    All filter should come from dimesion tables or DQ fact(only if required). 

     

    "To work correctly for aggregations, RLS expressions should filter both the aggregation table and the detail table."

     

    Thank you Both for the support .

Anonymous's avatar
Anonymous
Not applicable
6 years ago

mwegener 

Hi,

 

It's in DQ, how can provide data?

I can provide  2-3 excel, will that works.

 

Basically it's Manage aggregation model, you can refer below doc. also.

 

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-aggregations

 

I've created a composite model:

1. Fact on Direct Query

2. Aggregated table on Import 

3. Dimensions are on Dual mode.

I've one customer dimension where I've added role cust. name = " abvcfd", then I'm testing on desktop only

 

mwegener's avatar
mwegener
Most Valuable Professional
6 years ago

Hi Anonymous ,

 

does the user "DESKTOP-1234/ABC" have access rights to the database / tables?

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    mwegener 

     

    I don't think so i've define any roles in DW.

    So, I need to defines roles in DW on fact tables or all tables  or what level I have defines roles.

    Even if I select "Customer name"  roles  only then also it's failing.

    But even 

    • mwegener's avatar
      mwegener
      Most Valuable Professional
      6 years ago

      Hi Anonymous ,

       

      I think if you access the table with Direct Query and run this query as another user, the data will be queried with the appropriate user rights.

      The user therefore needs corresponding access rights (role) in the SQL database (DW).

      Unfortunately, I am not very familiar with this topic.

       

    • mwegener's avatar
      mwegener
      Most Valuable Professional
      6 years ago

      Hi Anonymous ,

       

      can you post a screenshot of the role definition?

       

    • v-lionel-msft's avatar
      v-lionel-msft
      Community Support
      6 years ago

      Hi Anonymous ,

       

      Does your RLS meet the following condition?

      "To work correctly for aggregations, RLS expressions should filter both the aggregation table and the detail table."

       

      Best regards,
      Lionel Chen

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        v-lionel-msft 

        mwegener 

         

        Found the problem.

        By misktake I've added report level filter from aggregated report after removing that filter RLS worrking fine.

        All filter should come from dimesion tables or DQ fact(only if required). 

         

        "To work correctly for aggregations, RLS expressions should filter both the aggregation table and the detail table."

         

        Thank you Both for the support .