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 

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

 

  • 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.

6 Replies

  • MasonMA's avatar
    MasonMA
    Super 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 

  • FBergamaschi's avatar
    FBergamaschi
    Super 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.
  • speedramps's avatar
    speedramps
    Super User
    1 year ago

    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's avatar
      johnfa
      Frequent Visitor
      1 year ago

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

  • techies's avatar
    techies
    Super User
    1 year ago

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

  • v-hashadapu's avatar
    v-hashadapu
    Community Support
    1 year ago

    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.