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
I have 3 tables:
I need to have the security filter on the userid field in the access table. This way, when a user logs in this will filter only the rows in the access table for that user, and the rows in tables t1 and t2 will only show the rows that the user has access to.
the t1 and t2 tables will have unique values in the access_id field.
the access table will have duplicate values in the access_id. But when we filter on a user the values in access_id is unique.
when creating the relationship i powerbi, he want's to create a one-to-many relationship from t1-to-access. In the filter direction field it's only possible to select single or both. I can't find any way to say that the filter direction should go from access-to-t1.
The model looks like below. This seems like a simple thing, but for some reason it's impossible to filter from many-to-one side.
One workaround is to select both in cross-filter direction and apply security filter in both directions. But this doesn't work when you have multiple tables connected to the access table.
Second workaround is to create the relationships as many-to-many. Then you can choose to filter from access-to-t1. But not sure if this will filter correctly when adding more tables.
Thanks for any suggestions 🙂
Solved! Go to Solution.
Great I've added some fake email addresses and used UserPrincipalName to apply the filter:
testrls - sam.pbix
You can impersonate an email by entering it into Other user and ticking the role you want to test:
If you are happy with this answer please mark as a solution for others to find !
Kudos are always appreciated! Check out our free Power BI video courses.
Great I've added some fake email addresses and used UserPrincipalName to apply the filter:
testrls - sam.pbix
You can impersonate an email by entering it into Other user and ticking the role you want to test:
If you are happy with this answer please mark as a solution for others to find !
Kudos are always appreciated! Check out our free Power BI video courses.
Did the above download work for you? @eirika
If you are happy with this answer please mark as a solution for others to find !
Kudos are always appreciated! Check out our free Power BI video courses.
hi,
Yes this works, thanks a lot 🙂
will this be usable on large datasets with million of records in the tables?
Should do, let us know how you get on!
If you are happy with this answer please mark as a solution for others to find !
Kudos are always appreciated! Check out our free Power BI video courses.
Hi @eirika
Under the RLS for t2 could you add a filter to AccessId=
AccessID IN Values(access[AccessID)
Same with t1
AccessID IN Values(access[AccessID)
This should directly apply filters onto those tables based on the list of ID the person has
If you are happy with this answer please mark as a solution for others to find !
Kudos are always appreciated! Check out our free Power BI video courses.
Hi,
Thanks for your response.
Sorry to say that this didn't work. It doesn't filter out anything, so the result is the same as without the new filters.
Hi @eirika Can you share a sample file? It works on my test file but the stucture could be different.
If you are happy with this answer please mark as a solution for others to find !
Kudos are always appreciated! Check out our free Power BI video courses.
hi,
thanks for your help
didn't find a way to include it here so uploaded a test file to
https://1drv.ms/u/s!Av4RVTN1GLuWgutkQzhr1KeJEP7bVQ?e=2MOgoq
Hi @eirika
How will the filtering work on publish?
Are you going to add emails into the Access table next to their UserId?
If you are happy with this answer please mark as a solution for others to find !
Kudos are always appreciated! Check out our free Power BI video courses.
No, I'm using the embedded solution.
So when the code call the EffectiveIdentity method from the application to open the PowerBI report we pass the role "User" and the value of "userId" for the logged in user. This give us possibility to use the username() function in RLS to get the userId value.
So the filter on the useraccess table in my example is hard coded to 1, this number will be replaced with username() on deploy.
the access table is only for limiting the rows the user has access to in t1 and t2. There will bo no extra columns.
I did get it to work now by using the filter below on t1 and t2 (replaces username() with hard coded value 1). But is this a nice and fast solution?
t1[access_id] IN CALCULATETABLE(DISTINCT(access[access_id]), access[userid] = USERNAME())
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 |
---|---|
90 | |
88 | |
83 | |
64 | |
49 |
User | Count |
---|---|
126 | |
110 | |
87 | |
70 | |
66 |