Forum Discussion

AK1982's avatar
AK1982
Frequent Visitor
6 years ago
Solved

Power BI Data RLS Security filter goes on and off by its own i.e. it applies some time and it doesn'

Hi,

 

I have an RLS filter applied between the sales table and sales manager table, this filter works some times and fails sometimes, the sales manager should only see data for a specific region.

 

Join condition between below tables are region id and relation defined is m:m and security filter applied on both directions and RLS filter defined on email i.e. email =userprincipalname() and published, in service portal some times filter applies to the user and at some time it doesn't.  i.e some time user can see his region sales and some times user can all-region sales.

 

Could you please let me know the reason for security filter failure

 

 

Complete Data Set -- https://1drv.ms/u/s!At07063_DV9mggQZ1MnvByW3u3dX?e=dORIfF

 

Relations Defined  --- https://1drv.ms/u/s!At07063_DV9mgX3MKjxgaQOfsCla

 

Could you please let me know the reason for this issue.

 
 

 

sales manager table:

 

User NameEmailRegion ID
sam[email protected] 0201
sam[email protected] 0202
sam[email protected] 0203
sam[email protected] 0204
jed[email protected]0202
jed[email protected]0204
henry[email protected]0202

 

Sales Data:

DateProductSale ammountRegion ID
03-01-2020Woo Single #2200201
03-01-2020Woo Single #150201
03-01-2020Woo Singles10.000201
03-01-2020Woo Album #425.000201
03-01-2020Woo Album #340.000201
03-01-2020Woo Album #255.000201
03-01-2020Woo Album #170.000201
03-01-2020Woo Logo85.000201
03-01-2020Woo Single #2200202
03-01-2020Woo Single #150202
03-01-2020Woo Singles10.000202
03-01-2020Woo Album #425.000202
03-01-2020Woo Album #340.000202
03-01-2020Woo Album #255.000202
03-01-2020Woo Album #170.000202
03-01-2020Woo Logo85.000202
03-01-2020Woo Ninja100.000202
03-01-2020Woo Single #2200203
03-01-2020Woo Single #150203
03-01-2020Woo Singles10.000203
03-01-2020Woo Album #425.000203
03-01-2020Woo Album #340.000203
03-01-2020Woo Album #255.000203
03-01-2020Woo Album #170.000203
03-01-2020Woo Logo85.000203
03-01-2020Woo Ninja100.000203
03-01-2020Woo Single #2200204
03-01-2020Woo Single #150204
03-01-2020Woo Singles10.000204
03-01-2020Woo Album #425.000204
03-01-2020Woo Album #340.000204
03-01-2020Woo Album #255.000204
03-01-2020Woo Album #170.000204
03-01-2020Woo Logo85.000204
03-01-2020Woo Ninja100.000204
Need Help
Show and Tell
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi AK1982 ,

     

    How about doing like this?

     

    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.

     

     

     

3 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    Hi AK1982 ,

     

    Believe you issue is related with the relationship.

     

    You have a many to many relationship with filter for both ways, this means that when you are maknig the look up from the user side you will get several regions for example Sam will get 4 regions however when you go to the other side from region to user if you pick up region 0204 you will get Sam and jed, this will mess up your filter and give you unexpected results.

     

    Have you tried to make a dimension table for region to make a one to many relationship?

    • AK1982's avatar
      AK1982
      Frequent Visitor
      6 years ago

      Hi Miguel,

       

      Thanks for the response, I have tried with region dimension as well, but its the same issue.

       

      Data Model now is : 

       

      Sales data table   -> M:1 -> Region Dimension -> 1:M -> sales manager table.

       

      Do I need to select  "apply security filter on both directions " while joining with dimension and sales manager table?

       

      Regards, 

      AK

       

       

       

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

        Hi AK1982 ,

         

        How about doing like this?

         

        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.