topic Re: Create Summary Table using one to many relationship in DAX Commands and Tips

Create Summary Table using one to many relationship

johnfa — Tue, 10 Jun 2025 13:27:34 GMT

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

Re: Create Summary Table using one to many relationship

speedramps — Tue, 10 Jun 2025 13:50:47 GMT

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.

Re: Create Summary Table using one to many relationship

MasonMA — Tue, 10 Jun 2025 13:57:57 GMT

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

Re: Create Summary Table using one to many relationship

johnfa — Tue, 10 Jun 2025 14:10:04 GMT

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

Re: Create Summary Table using one to many relationship

techies — Tue, 10 Jun 2025 16:36:31 GMT

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

Re: Create Summary Table using one to many relationship

v-hashadapu — Wed, 11 Jun 2025 09:39:02 GMT

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.

Re: Create Summary Table using one to many relationship

FBergamaschi — Wed, 11 Jun 2025 11:58:54 GMT

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 ]

)