Calculation for all but selected value using row level security

Question

I am implementing row level security on a data model that includes aggregate tables. I am using the aggregate tables to report the totals for my organization. However, I also want to show a rate for all programs but the selected program that is being filtered via row level security. I want the calculation to be dynamically populated. Please see the table below for sample data.&nbsp;yr_trmstu_divrolekeycohort_pretain_pCohort_SumRetain_SumCalculated RateLevel&nbsp;Calculate Rate for All but Selected GRANMS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1617FAGRGRPMGTfall_2016_cohortfall16_fall17_ret32022069%GR Program&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1617FAGRGRISEMfall_2016_cohortfall16_fall17_ret23616469%GR Program&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1617FAGRGRLTMSfall_2016_cohortfall16_fall17_ret161275%GR Program&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1617FAGRGRANMSfall_2016_cohortfall16_fall17_ret17213478%GR Program69%&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1617FAGRGRCSMSfall_2016_cohortfall16_fall17_ret503468%GR Program&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;GR Total&nbsp;&nbsp;&nbsp;79456471%All GR Program&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1617FAUGUGINSCfall_2016_cohortfall16_fall17_ret926267%UG Program&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1617FAUGUGCISCfall_2016_cohortfall16_fall17_ret583662%UG Program&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1617FAUGUGGSTCfall_2016_cohortfall16_fall17_ret6467%UG Program&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1617FAUGUGIMEDfall_2016_cohortfall16_fall17_ret242083%UG Program&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1617FAUGUGMEBAfall_2016_cohortfall16_fall17_ret281657%UG Program&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1617FAUGUGBIOTfall_2016_cohortfall16_fall17_ret302687%UG Program&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1617FAUGUGANLYfall_2016_cohortfall16_fall17_ret200%UG Program&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1617FAUGUGUNDCfall_2016_cohortfall16_fall17_ret16850%UG Program&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UG Total&nbsp;&nbsp;&nbsp;25617267%All UG Program&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Grand Total&nbsp;&nbsp;1050736&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Selected Program in Bold&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

v-joesh-msft · Answer

Hi&nbsp;Anonymous&nbsp;,As this post says:"The only solution to the problem is to create static measures or a separate table where you hold certain, company-wide metrics/aggregations/calculations. This separate table should not be tied to RLS and then you can use the aggregated metrics in that table in your calculations.&nbsp;"Best Regards,Community Support Team _ JoeyIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Calculation for all but selected value using row level security

1 Reply

Related content

Quarter calculation upto selected month

ROW LEVEL SECURITY with forced selection slicer single select

SELECT permissions or external policy action error. OneLake Security issue

Role Level Security

Governance & Security