Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
Solved! Go to Solution.
You want it in DAX, here it is
You want it in DAX, here it is
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.
Hi @johnfa As i understand, have you tried using left outer join in power query?
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
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
10 | |
10 | |
9 | |
9 |
User | Count |
---|---|
20 | |
13 | |
12 | |
11 | |
8 |