Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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(
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
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.
3) I created this calculated column on table Sales:
"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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
82 | |
70 | |
49 |
User | Count |
---|---|
143 | |
123 | |
107 | |
61 | |
55 |