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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors