Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi,
I have three tables:
Table 1 ID, Email
4, amy1000@xxxxx.com
Table 2 ID, SalesRep, AccountID, Product
1, Mike, 1001, A
1, Mike, 1002, A
1, Mike, 1003, A
2, Smith, 2001, A
2, Smith, 2002, A
2, Smith, 2003, A
3, John, 1001, B
3, John, 1002, B
3, John, 1003, B
4, Amy, 2001, B
4, Amy, 2002, B
4, Amy, 2003, B
Table 3 AccountID
1001
1002
1003
2001
2002
2003
I use Table 1 and 2 in RLS to get the group of accounts that each sales rep can see (e.g., Table 1 link to Table 2 and then ‘Table1’[Email]= USERPRINCIPALNAME() ).
Data model for dashboard starts with Table 3 to filter accountID and link to other tables. The reason I can’t link from Table 3 to Table 2 is many-to-many relationship that will cause troubles later(one account has multiple sales reps for different products).
Now the only thing I need to do is in RLS, write DAX for Table 3 and say -
in Table 3, I only need AccountIDs in Table 2 (already filtered by RLS). So if Mike logs in, he will have 1001, 1002, and 1003 in Table 2, also same three accounts in Table 3 and those are the data he can see in the dashboard.
I tried something like following for Table 3 but didn’t work
VAR _accts=VALUES(Table2, [AccountID])
Return
If (‘Table 3’[AccountID] in _accts, TRUE())
How to write this Dax? Thanks!
I did create a measure like following and used it as a filter for each visual, it worked but I had to use it for every visual and every slicer on every page, it's not a good solution.
Any help is really appreciated.
YL
Solved! Go to Solution.
As you want to filter Table 3 based on the sales reps' access to accounts in Table 2 using Row-Level Security (RLS), you can use below DAX in your Table 3 filter to check if each AccountID is included in the list of AccountIDs accessible to the current user through Table 2.
Filter Table 3 by Sales Rep Access =
VAR AllowedAccounts =
CALCULATETABLE(
VALUES('Table 2'[AccountID]),
FILTER(
'Table 2',
'Table 2'[SalesRep] = USERNAME() // Assuming SalesRep is the column representing the sales representative's name
)
)
RETURN
FILTER(
'Table 3',
'Table 3'[AccountID] IN AllowedAccounts
)
Then use this DAX as a row level security on table 3 in power BI.
Thanks!
Inogic Professional Service Division
An expert technical extension for your techno-functional business needs
Power Platform/Dynamics 365 CRM
Drop an email at crm@inogic.com
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
Samlnogic - your dax code works well, thanks!!
As you want to filter Table 3 based on the sales reps' access to accounts in Table 2 using Row-Level Security (RLS), you can use below DAX in your Table 3 filter to check if each AccountID is included in the list of AccountIDs accessible to the current user through Table 2.
Filter Table 3 by Sales Rep Access =
VAR AllowedAccounts =
CALCULATETABLE(
VALUES('Table 2'[AccountID]),
FILTER(
'Table 2',
'Table 2'[SalesRep] = USERNAME() // Assuming SalesRep is the column representing the sales representative's name
)
)
RETURN
FILTER(
'Table 3',
'Table 3'[AccountID] IN AllowedAccounts
)
Then use this DAX as a row level security on table 3 in power BI.
Thanks!
Inogic Professional Service Division
An expert technical extension for your techno-functional business needs
Power Platform/Dynamics 365 CRM
Drop an email at crm@inogic.com
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
76 | |
63 | |
51 | |
47 |
User | Count |
---|---|
214 | |
84 | |
61 | |
61 | |
60 |