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!