Row level security: User should see own data and total

[zvm]

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!

[Greg_Deckler]

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.

[zvm]

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.

[Greg_Deckler]

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

[zvm]

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 countriesDenmark, with RLS in action

[v-jiascu-msft]

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

[zvm]

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

[v-jiascu-msft]

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

[zvm]

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

[zvm · Accepted 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).