Forum Discussion
Using RLS with a negative filter
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(
6 Replies
"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.
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 RegardsLucien
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.
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 =VARCompaniesWithASale = CALCULATE(COUNT(Sales[Sale_Id]),FILTER(all(Companies),Companies[CompanyName]=[User]))RETURNIF(ISBLANK(CompaniesWithASale),1,0)4) I created this RLS role:[User_Id] = 25) I apllied page filter:BuyCount = 1When 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!
