Forum Discussion

johnfa's avatar
johnfa
Frequent Visitor
1 year ago
Solved

Create Summary Table using one to many relationship

Hi there  I am looking to create a summary table based on 2 tables with a one to many relationship The sample tables below are Person and Activity joining on PersonID  PersonID Name   Ac...
  • FBergamaschi's avatar
    FBergamaschi
    1 year ago

    You want it in DAX, here it is

     

    Table =
    SELECTCOLUMNS(
        GENERATEALL(
            SELECTCOLUMNS(
                Person,
                Person[Name],
                "ID",
                Person[PersonID]
            ),
            CALCULATETABLE(Activity)
        ),
        [ID],
        Person[Name],
        Activity[Activity ]
    )
    If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
    Thank you.
FBergamaschi's avatar
FBergamaschi
Icon for Super User rankSuper User
1 year ago

You want it in DAX, here it is

 

Table =
SELECTCOLUMNS(
    GENERATEALL(
        SELECTCOLUMNS(
            Person,
            Person[Name],
            "ID",
            Person[PersonID]
        ),
        CALCULATETABLE(Activity)
    ),
    [ID],
    Person[Name],
    Activity[Activity ]
)
If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.