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

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