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.Permissi...
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

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