Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
olivdata
Frequent Visitor

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_IdClientName
0John
1Josh
2Mary
3Sandra

 

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

 

Sales    
Sale_IdClient_IdCompany_IdDateValue
0322021_12_1099.00
1322021_11_0530.00
2232021_11_0331.00
3232021_10_0732.00
4142021_10_0733.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.
 
6 REPLIES 6
v-luwang-msft
Community Support
Community Support

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 )

vluwangmsft_0-1646273976889.png

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

vluwangmsft_1-1646274057621.png

 

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

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
Super User
Super User

"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 @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.

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

Yes, I do. Could you explain it? Thanks

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.