Forum Discussion
Helper IIRow level security: User should see own data and total
Hi,
My customer wants row level security, but with addition that all users can see total amount as well. So, let's say, we have table like this:
Products
Country Measure
Italy 65
Germany 72
Spain 60
France 54
Total 251
User from Italy shoud see something like this:
Italy Total
Products 65 251
or like this:
Products
Country Measure
Italy 65
Rest 186
Total 251
or like this, without Rest (but it would be nice to have "Rest" :))
Products
Country Measure
Italy 65
Total 251
So, user from Italy should see Italy numbers and Total, but not a details per other countries.
And of course, users from other countries should follow the same rule respectively.
Part of the problem is that they could have like hundred or more such a users (it it not countries in real example, but sales locations).
I can make two fact tables - one normal and apply RLS and the other without country dimension that will have total for all countries across all other dimensions. But, two tables solution has some disadvantages:
- I am not sure how that will function on a reports
- more work on maintenance
- maybe some other issues
Is it possible to have a solution for this with just one fact table?
Thank you!
Here is the solution:
https://www.sqlbi.com/articles/implement-non-visual-totals-with-power-bi-security-roles/
Basicaly like mine. I just forgot I can put all measures together and hide additional calculated table (with summarizecolumns).
9 Replies
- Greg_Deckler
Community Champion
Create a calculated column in your fact table that does a SUM of all sales, so something like:
Total Sales = CALCULATE(SUM([Column]),ALL(Table))
Then, regardless of what row they have access to, you can display total sales.
zvmThanks for the reply.
I thought of that, but it does not work.
Calculated column cannot store total value for one dimension. It stores just the same value as original measure. I assume it is due to row context. SO, it cannot be used at all.
Measure is ok for the purpose of calculating share in total per dim members. But, if RLS is applied, it is applied on a measure as well.
Maybe I am doing something wrong, but here are my measure and calc column (formula is the same):
TotalSalesAllCountries_Column = CALCULATE([TotalSalesAmount];ALL(Geography))
Measure works fine on a report if I am admin user who can see everything. Calc. column displays just amount for a particular country.
- Greg_Deckler
TotalSalesAllCountries_Column = CALCULATE(SUM([TotalSalesAmount]);ALL(Geography))
