Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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).
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.
Thanks 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.
TotalSalesAllCountries_Column = CALCULATE(SUM([TotalSalesAmount]);ALL(Geography))
TotalSalesAmount is a measure.
TotalSalesAmount = SUM(Sales[SalesAmount])
So, actually my calc column formula is:
TotalSalesAllCountries_Column = CALCULATE(SUM(Sales[SalesAmount]);ALL(Geography))
Here is the report. MEasure and calc column have the same formula.
Hi @zvm,
The solution could be storing these values in a calculated table. In my demo, it looks like this.
Table 12 = CALCULATETABLE ( ADDCOLUMNS ( { "Values" }, "Amount", SUM ( Sales[Quantity] ), "Sales", SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] ) ) )
Best Regards,
Dale
Hi Dale,
Thanks for the suggestion. But that is solution with another table. Or am I missing something?! 🙂
I kind of solve it myself with another table without one dimension I wanna hide (Geography in this case).
But with this solution I have to maintain two tables instead of one (actually to have two sets of measures) Maybe that is the only way (or the best)?
Hi @zvm,
It's a calculated table rather than a duplicated table. Did you notice that there are only three values in the calculated table. Surely you can add more values you want. The advantage is you don't need to maintain it. It will recalculate when the data change.
Best Regards,
Dale
Hi Dale,
I am not sure I am following you. Or rather I cannot see how one calculated table solves the issue here.
I need to have (let's say) country dimension in order that user from each country can see his/her numbers. At the same user must be able to see total for all countries, but not details for other countries. How can I hide other countries depending on user but at the same time keeping total number for all those countries?
Calculated table or summarized table just calculates totals without one dimension (country for instance). That is what I need, but than I lack details for country of user who is logged in.
Please check my first post.
I would like I am missing the point and that there is a solution. 🙂 But, I just don't see it yet. 😞
Best regards
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).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.