Forum Discussion

olivdata's avatar
olivdata
Frequent Visitor
4 years ago

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 ne...
Need Help
Tips and Tricks
v-luwang-msft's avatar
v-luwang-msft
Icon for Community Support rankCommunity Support
4 years ago

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's avatar
    olivdata
    Frequent Visitor
    4 years ago

    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!