From SQL to DAX

[Anonymous]

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!

[v-juanli-msft]

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.

Join tables:from SQL to DAX

RLS with power bi

Best Regards

Maggie

[Anonymous]

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!

[v-juanli-msft · Accepted 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