Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I want to filter a table when:
Id in (select EntityId from ShipmentItemPermissionGroup sipg join PermissionGroup pg on pg.Id = sipg.PermissionGroupId join UserPermissionGroup upg on upg.PermissionGroupId = pg.Id join [User] u on u.Id = upg.UserId where u.Username = 'username')
I am adding this for Role permissions. I have tried various ways but unable to get this to work. Can someone suggest a solution for this?
Thanks!
Solved! Go to Solution.
Hi @Anonymous
For this SQL statement, i could obtain a JOIN behavior in DAX using the relationships in data model.
select EntityId from ShipmentItemPermissionGroup sipg join PermissionGroup pg on pg.Id = sipg.PermissionGroupId join UserPermissionGroup upg on upg.PermissionGroupId = pg.Id join [User] u on u.Id = upg.UserId where u.Username = 'username'
edit relationships between the four tables used in the statement based on
UserPermissionGroup[UserId]<->User[Id]
UserPermissionGroup[PermissionGroupId]<->PermissionGroup [Id]
PermissionGroup [Id]<->ShipmentItemPermissionGroup[PermissionGroupId]
Then click on "New Table", enter the formula below
Table = SELECTCOLUMNS ( FILTER ( SUMMARIZE ( ShipmentItemPermissionGroup, ShipmentItemPermissionGroup[EntityId], User[name] ), [name] = "a" ), "id", [EntityId] )
Best Regards
maggie
Hi @Anonymous
From you SQL statement, it seems you are seeking for the ways to join tables and filter the id which meets the conditions, then finally to apply this filter id for RLS.
Please refer to the following articles. If you have difficulties in implementing this in your scenario, please share some data and expected results so that i can analyze for you effeciently.
Best Regards
Maggie
Hi Maggie,
Thanks for your response, I am able to get to the penultimate step but the last join is feeling impossible:
[PermissionGroupId] IN (SELECTCOLUMNS (FILTER(CROSSJOIN(User, UserPermissionGroup), User[Id] = UserPermissionGroup[UserId] && User[Username]="username"), "PermissionGroupId",[PermissionGroupId]))
I want to do a further join on PermissionGroupId to ShipmentItemPermissionGroup.PermissionGroupId but do not know how to do it.
Original query:
Id in (select EntityId from ShipmentItemPermissionGroup sipg join PermissionGroup pg on pg.Id = sipg.PermissionGroupId join UserPermissionGroup upg on upg.PermissionGroupId = pg.Id join [User] u on u.Id = upg.UserId where u.Username = 'username')
Please help. Thanks!
Hi @Anonymous
For this SQL statement, i could obtain a JOIN behavior in DAX using the relationships in data model.
select EntityId from ShipmentItemPermissionGroup sipg join PermissionGroup pg on pg.Id = sipg.PermissionGroupId join UserPermissionGroup upg on upg.PermissionGroupId = pg.Id join [User] u on u.Id = upg.UserId where u.Username = 'username'
edit relationships between the four tables used in the statement based on
UserPermissionGroup[UserId]<->User[Id]
UserPermissionGroup[PermissionGroupId]<->PermissionGroup [Id]
PermissionGroup [Id]<->ShipmentItemPermissionGroup[PermissionGroupId]
Then click on "New Table", enter the formula below
Table = SELECTCOLUMNS ( FILTER ( SUMMARIZE ( ShipmentItemPermissionGroup, ShipmentItemPermissionGroup[EntityId], User[name] ), [name] = "a" ), "id", [EntityId] )
Best Regards
maggie
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |