Create Summary Table using one to many relationship

[johnfa]

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		ActivityID	PersonID	Activity
1	Tom		1	1	Running
2	Steve		2	1	Swimming
3	Jim		3	1	Cycling
			4	3	Cycling
			5	3	Climbing

 I want to produce a summary table that looks like this.

PersonID	Name	Activity
1	Tom	Running
1	Tom	Swimming
1	Tom	Cycling
2	Steve
3	Jim	Cycling
3	Jim	Climbing

I have played around with Summarize / AddColumn but so far been unable get the desired results 

Any help greatly appreciated 

thanks
John

[FBergamaschi · Accepted solution]

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.

[v-hashadapu]

Hi , Thank you for reaching out to the Microsoft Community Forum.

Please refer attached .pbix file for reference and share your thoughts.

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.

[techies]

Hi @johnfa As i understand, have you tried using left outer join in power query?

[MasonMA]

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 

[speedramps]

Hi @johnfa 

I want to help you but your problem description is too vague

You say "I am looking to create a summary table based on 2 tables with a one to many relationship"

But you have not provided examples of the 2 tables or the relationships.

[johnfa]

thanks @speedramps but the tables are in the original message arent they ?