Forum Discussion

ryan_b_fiting's avatar
ryan_b_fiting
Icon for Post Patron rankPost Patron
4 years ago
Solved

Row Level Security - Allows aggregated data to be seen

Hello Community - 

I have a rather complex problem I think.  So I am setting up my RLS for my reporting that is being rolled out to multiple practices and multiple teams.  Some of the metrics we track Ranks among peers in your practice, Top ranked provider metrics (no names provided, just the metric) so the providers can see how they are tracking in terms of rank and versus the Top dogs numbers.

 

My question comes in here.  Is there a way to keep my dynamic RLS using the UPNs, but still allow the rankings, the Top Provider metrics and the overall average of the practice for the specific metrics?

 

Currently, when I test it out, my provider rank is always 1 out of 1, the Top metric is my metric and the average value is my value.

 

Any help on this would be greatly appeciated.  I am trying to roll this out in the next 2 weeks and am having a hard time figuring out a solution to this.

 

Thanks in advance for any help!

Ryan F.

data protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • lukiz84's avatar
    lukiz84
    4 years ago

    Sorry, you are right. It's because of "lineage".

     

    Don't create relationships here, but in your Measure for NPE for example do this:

     

    AggregateNPENoRLS = 
    CALCULATE(
        SUM(Aggregate_[NPEs]),
        TREATAS(VALUES(Date_Table[Date]), Aggregate_[Date]),
        TREATAS(VALUES(Provider_Table[Provider Name], Aggregate_[Provider Name])
    )

     

    (you can also use AVG(Aggregate_[NPEs]) - I think that would make more sense in this case.

     

    TREATAS transfers the filter to the Aggregate_ Table without having to use a relationship. (thats becase of lineage - if you want to learn more about it, see Understanding data lineage in DAX - SQLBI)

     

    BR

     

10 Replies

  • lukiz84's avatar
    lukiz84
    Icon for Memorable Member rankMemorable Member
    4 years ago

    Hi, it's not that complex.

     

    Easiest way is to create a calculated table with those aggregated data and don't put any RLS on it.

    Then you just need to create relationships to your dimension table and create some measures on this table.

     

    BR

    • ryan_b_fiting's avatar
      ryan_b_fiting
      Icon for Post Patron rankPost Patron
      4 years ago

      Won't those measures automatically be filtered by the RLS prior to the table being calculated?

       

      • lukiz84's avatar
        lukiz84
        Icon for Memorable Member rankMemorable Member
        4 years ago

        Nope, calculated tables are calculated when refreshing the data model. And the data model (when refreshing) doesn't know which user connects to the report, hence no RLS on it 😉

         

        BR