Sales ,Profit, Column level security

Power BI
1

So I have a single table (FactSales) with Sales Value and Profit.

The issue is everyone can see sales for their region
However I want some users to be able to view sales but not profit.

My plan was

  • Create a cut down version of FactSales called FactProfit
  • Remove the column Profit from FactSales
  • Join FactSales to FactProfit

Then the I was hoping I could do like a SQL left join so if there is a value for Profit shpow it. If not zero it.

However in the model it will not let me filter in one direction for a one to one relationship. So when I run a query the security on the profit table means I get no results back from my Sales Table.

Is there a way to deliver Column Level Security In any way \ shape ?
Thanks
E

2

@ells, Well maybe perspectives can solve your problem.
Watch out this video and let us know if you get any hint for the solution:

3

Watched it. I was thinking there might be a way to mix perspectives and security but at 10 minutes in Patrick says “they are not for security. They are not a security feature”.

If there is a way for the users to see the Profit they will find it. I am truly looking for a way to secure this data.

I am working on an alternative way but that will take up space in the model and my model is already at bursting point.

Thanks
E

4

Column-level security is a bit of a hack - to my understanding.

Below are some resources that have discussed this that I’ve run across.
Important Note: I have never needed to use these techniques myself.

This first link calls out the specific issue with the bi-directional filter created between FactSales and FactProfit, and shows how to potentially fix that.
https://community.powerbi.com/t5/Community-Blog/Column-Level-Security/ba-p/1309392

This final link fixes the issue, but I’m guessing it’s going to impact your model because it doubles the row count:
https://dataveld.com/2018/02/10/unpivoting-sensitive-columns-in-power-bi-to-apply-row-level-security/

5

Hi @ells

I’ve provided solution for Column level security. You also can try

Let us know if it works for you.

6

@Heather
Thanks . I had missed the last articvle there. As my report cant cope with the volume of data I cant see unpivoting will work.
My current tack is to splt the fact table into two. The profit just having the Surrogate key and Profit. Then I can join to the fact table. The new profit table has one row with zero for profit and one row with the real value.

This should work but I just cant get the data into the model. Its timing out again.
:frowning:
E

7

@Rajesh
Thanks - that has sent me down a different train of thought. When I show profit if I could dispaly zero for people without permsiion and the real value for people with permission that might work.

I need to be carefull as it must be secure and also must not break any visuals which contain profit.

Ok, the method posted wont work (unless I read the post wrong) due to volumes of data. I have real performance issues so anything that adds to size of the model in a substantial way is not an option.

E

8

Hi @ells

Try this
Create Users table like below
UsersTable

And Measure logic

Total Profit
Total Profit1012×286 20.4 KB

Attached sample PBIX file, Publish the report to Service and test, you can’t test from desktop.

Profit.pbix (399.8 KB)

1 Like
9

Thanks,
I think this will be our solution as I dont think we can add anything else into the data model. Its not ewhat I really want but I think the perfect solution would add too much to the data model size

Thanks
E

10

A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!