Using RLS with a negative filter

[olivdata]

Hello!

I have a market report with sales (table Sales) of diferent companies (table Companies) for different clients (table Clients).

So, I want to use a page to show sales for clients that have never bought from a certain company and control this by RLS.

Example:

Clients
Client_Id	ClientName
0	John
1	Josh
2	Mary
3	Sandra

Companies
Company_Id	CompanyName
0	Company X LLC
1	Company Y LLC
2	Commpany Z LLC
3	Company A1 LLC
4	Company A2 LLC

Sales
Sale_Id	Client_Id	Company_Id	Date	Value
0	3	2	2021_12_10	99.00
1	3	2	2021_11_05	30.00
2	2	3	2021_11_03	31.00
3	2	3	2021_10_07	32.00
4	1	4	2021_10_07	33.00

On this example, we have three Clients that had never bought from the Company_Id number 2 (Commpany Z LLC). These are the Client_Id number 0, 1 and 2.

I want to show just this Sales on this page. It's a way to show how clients could be prospected by this company.

I got this using a calculated column:

BuyCount = CALCULATE(

COUNT(Sales[Sale_Id]),

FILTER(all(Companies),Companies[Company_Id]=2))

And apllying it on the page filter:

MyMeasure is blank

And it works! Now, I want to set a Company_Id for each Power BI user to filter the data just for this page, but not to entire report.

Could you help me to know how can I do it?

Thanks in advance.

[v-luwang-msft]

Hi @olivdata ，

You could test like the below:

create the below measure on sale table:

BuyCount1 = CALCULATE(
COUNT(Sales[Sale_Id]),
ALLSELECTED(Companies))

  Then create a measure on clients table:

test = IF([BuyCount1]=BLANK(),1,BLANK())

 And if you want to use slicer ,and choose the company easiler, you could not use rls ,because test is a measure,if you want to use rls ,you need to work on column.

And create the below vusal:

(When I choose 2,it return me 0,1,3,it means ,client 0,1,3 not  bought from the Company_Id number 2 )

(When I choose 2 and 3,it return me 0,1,it means ,client 0,1not  bought from the Company_Id number 2 and  number 3 )

And if you still want to  set a Company_Id for each Power BI user to filter the data just for this page,and I think you need to create a table of customer companies, each customer corresponds to all companies, and then filter, when a purchase has been made, then blank, no purchase has been made, then 1, filter out potential users.

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

Best Regards

Lucien

[olivdata]

Hi Lucien @v-luwang-msft ! Thanks for your help. I still have the problem. But, I tried with another strategy changing your sample pbix to show it.

https://we.tl/t-ow2yoAAYdK 

1) I duplicated the table Companies with name Userlist. Columns User_Id, UserName.

2) I created this measure to pick just the company name I want to exclude:

User = LASTNONBLANK(Userlist[UserName],NOT(ISBLANK(Userlist[UserName])))

3) I created this calculated column on table Sales:

BuyCount2 =

VAR

CompaniesWithASale = CALCULATE(COUNT(Sales[Sale_Id]),FILTER(all(Companies),Companies[CompanyName]=[User]))

RETURN

IF(ISBLANK(CompaniesWithASale),1,0)

4) I created this RLS role:

[User_Id] = 2

5) I apllied page filter:

BuyCount = 1

When I test it showing my report as the user I've created on RLS roles, [User] variable is correct (Commpany Z LLC), but the page filter does not work properly.

Alternatively, if I set the company name at [User] measure, like this...

[User] = "Commpany Z LLC"

... it works fine! But using with the step 2 formula it doesn't work.

Could you review my new file? I think we can do a good resource here.

Thanks a lot!

[lbendlin]

"On this example, we have three Clients that had never bought from the Company_Id number 2 (Commpany Z LLC). These are the Client_Id number 0, 1 and 3."

Did you mean to say clients 0,1 and 2 ?

I don't think you want to use RLS in this scenario. A measure would be more appropriate in my opinion.

[olivdata]

Hi @lbendlin ! Thanks for your attention.

You're right. I fix it on my post. We are looking for the solution yet but I think it's hard.

[lbendlin]

Would you be willing to follow my suggestion and use measures instead of RLS?

[olivdata]

Yes, I do. Could you explain it? Thanks