Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
johnfa
Frequent Visitor

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 

PersonIDName ActivityIDPersonIDActivity 
1Tom 11Running
2Steve 21Swimming 
3Jim 31Cycling
   43Cycling
   53Climbing


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

PersonIDNameActivity 
1TomRunning
1TomSwimming 
1TomCycling
2Steve 
3JimCycling
3JimClimbing


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

Any help greatly appreciated 

thanks
John

 

1 ACCEPTED SOLUTION
FBergamaschi
Helper I
Helper I

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.

View solution in original post

6 REPLIES 6
FBergamaschi
Helper I
Helper I

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
Community Support
Community Support

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
Solution Sage
Solution Sage

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

― Power BI | Microsoft Fabric | PL-300 | DP-600 | Blog: medium.com/@cseprs_54978
MasonMA
Resolver I
Resolver I

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
Community Champion
Community Champion

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.

 

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.