Row Level Security - Allows aggregated data to be seen

[ryan_b_fiting]

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.

[kaisrec]

https://community.fabric.microsoft.com/t5/Desktop/Aggregated-Measures-with-RLS/m-p/101813

[lukiz84]

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]

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

[lukiz84]

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

[ryan_b_fiting]

I tried it and unfortunately that does not work.  Would you suggest using CALCULATETABLE() or SUMMARIZE() function to create your table?  I cannot get the CALCULATETABLE() to work, but the SUMMARIZE() is way off.

My rankings are created in tabular editor as well because we are ranking 40+ metrics.  But I do not think that should matter because we can just select what Calc Item we are looking at.

Do you have a sample of a table you would cacluate to do this?  

[lukiz84]

CALCULATETABLE is not / can't be used to create a calculated table.

I can share a sample from my own tabular model:

SUMMARIZECOLUMNS(
        'DimTable'[DimensionXY],
        'DimTable'[DimensionYZ],
        'Dates'[Date],
        "RevenueActual", [RevenueActual],
        "CostsActual", [CostsActual],
        "RevenueBudget", [RevenueBudget],
        "CostsBudget", [CostsBudget]
)

[ryan_b_fiting]

Thanks for the information.  I think there is too many limitations with this, but I may just not completely understand the solution.

So if I were to use this, when I am looking at a specific date range, this aggregated table will not show the proper data within that range correct?  Same with looking at specific practices because we cannot make the joins there.  So the rankings and Top Provider numbers will not be displaying correct.

Is that a correct assessment, or am I not understanding properly?

Thanks again for your input.

[lukiz84]

Thanks for the information.  I think there is too many limitations with this, but I may just not completely understand the solution.

> No limitations, you just have to make the correct relations 🙂 (EDIT: No relationship, Transfer the filter via TREATAS)

So if I were to use this, when I am looking at a specific date range, this aggregated table will not show the proper data within that range correct?  Same with looking at specific practices because we cannot make the joins there.  So the rankings and Top Provider numbers will not be displaying correct.

> For the date range: Sure, that's why I included the date in SUMMARIZECOLUMNS, you can just connect your Date-Table to this field after creating the calculated table. Then everything is filtered according to your date (range) selection 🙂 (EDIT: No relationship, Transfer the filter via TREATAS) 

> For specific practices: Sure, just add your key column from the "Practices" Table (i assume it's called like that) in SUMMARIZECOLUMNS, then you can connect your key to this field afterwards and filter it accordingly. (EDIT: No relationship, Transfer the filter via TREATAS) 

I think you get the point now 🙂

Is that a correct assessment, or am I not understanding properly?

> Not correct 😉

Thanks again for your input.

> You are very welcome, I also was stuck at those points a while ago 🙂

[ryan_b_fiting]

You cannot join the tables though because it will create a circular dependency right.

[lukiz84 · Accepted solution]

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