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.
MasonMA's avatar
MasonMA
Icon for Super User rankSuper User
1 year ago

HI johnfa 

 

I would use Power Query to merge these two tables instead of using DAX to create a Summary table. You can do it throught UI in Power Query, which is super convienient. Below is the code it generated in Query Editor. 

let
    Source = Table.NestedJoin(Table1, {"PersonID"}, Table2, {"PersonID"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"ActivityID", "PersonID", "Activity "}, {"Table2.ActivityID", "Table2.PersonID", "Table2.Activity "}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table2",{"Table2.ActivityID", "Table2.PersonID"})
in
    #"Removed Columns"

 

Thanks

Mason