cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zvm Regular Visitor
Regular Visitor

Row 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" Smiley Happy)

 

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
zvm Regular Visitor
Regular Visitor

Re: Row level security: User should see own data and total

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 9
Super User
Super User

Re: Row level security: User should see own data and total

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


zvm Regular Visitor
Regular Visitor

Re: Row level security: User should see own data and total

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.

Super User
Super User

Re: Row level security: User should see own data and total

TotalSalesAllCountries_Column = CALCULATE(SUM([TotalSalesAmount]);ALL(Geography))

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


zvm Regular Visitor
Regular Visitor

Re: Row level security: User should see own data and total

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.

All_countries,jpg.JPGAll countriesDenmark.JPGDenmark, with RLS in action

Community Support Team
Community Support Team

Re: Row level security: User should see own data and total

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] )
    )
)

Row_level_security_User_should_see_own_data_and_total

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
zvm Regular Visitor
Regular Visitor

Re: Row level security: User should see own data and total

Hi Dale,

Thanks for the suggestion. But that is solution with another table. Or am I missing something?! Smiley Happy

I kind of solve it myself with another table without one dimension I wanna hide (Geography in this case).

 

RLS with another table.png

 

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)?

 

 

Community Support Team
Community Support Team

Re: Row level security: User should see own data and total

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
zvm Regular Visitor
Regular Visitor

Re: Row level security: User should see own data and total

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. Smiley Happy But, I just don't see it yet. Smiley Sad 

 

Best regards

Highlighted
zvm Regular Visitor
Regular Visitor

Re: Row level security: User should see own data and total

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).