Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

From SQL to DAX

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!

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-juanli-msft
        Community Support
        7 years ago

        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