Forum Discussion
RLS with multi-tenant data and overall aggregates
Is this possible with Power BI?
If I understand RLS correctly, all of my data has a tenant-id which I can use to filter by customer....So I can give customer 1 their data, customer 2 theirs but Nobody linked to Customer 1 can see data for Customer 2.
However, I would like the customers to be able to compare their aggregates against "overall" aggregates obtained from all data from all customers.
Something like this:
Customer 1 sees:
No. of widgets this month: 20
Overall Average widgets this month: 16.8
Performance this month: +3.2
Customer 2 sees:
No of widgets this month: 15
Overall Average widgets this month: 16.8
Performance this month: -1.8
ie, all customers share the average number of widgets, but they only see their own widget totals
Hi horizondave,
I'm afraid this is restricted by the way how RLS works. Maybe you can add many attributes in one calculated table like this:
Table = ADDCOLUMNS ( { "Summarized Values" }, "Total Sales Quantity", SUM ( 'Sales'[Quantity] ), "Total Sales Amount", SUMX ( Sales, Sales[Net Price] * Sales[Quantity] ), "Number of kinds of Products", DISTINCTCOUNT ( 'Product'[Brand] ) )Best Regards!
Dale
5 Replies
v-jiascu-msftMicrosoft Employee
Hi horizondave,
The solution could be that you can add the Overall as a calculated column. Or create a summarized table. You can try it out.
Best Regards!
Dale
The trouble is I potentially have *lots* of these attributes I want to add as averages....It's a very time consuming option to have to create each one individually.
- v-jiascu-msft
Hi horizondave,
I'm afraid this is restricted by the way how RLS works. Maybe you can add many attributes in one calculated table like this:
Table = ADDCOLUMNS ( { "Summarized Values" }, "Total Sales Quantity", SUM ( 'Sales'[Quantity] ), "Total Sales Amount", SUMX ( Sales, Sales[Net Price] * Sales[Quantity] ), "Number of kinds of Products", DISTINCTCOUNT ( 'Product'[Brand] ) )Best Regards!
Dale