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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
BarryFletcher
Helper II
Helper II

Match & Sum values from another table?

Hi all,

 

I am looking for help please?

 

I have 2 tables - 1 table has a column called Line number.

My second table also has a column called line number but it could have 5 rows with that lne number.

How can I list the 5 items from the second table

and sum the lunghts based on the line number in the first table?

 

So table one would contain something like this:

 

NameLine Number
  
Barry12345
Tom45678

 

and table 2 would have data such as below:

 

Line number Length
123451
123452
456785
456783

 

I would like to get the sum of rows from table 2, based on the line number value in table 1?

 

Hope that makes sense?

1 ACCEPTED SOLUTION
audreygerred
Super User
Super User

Hi! Your first table, the one with name and line number (line number being unique) is your dimension table. Your second table (the one with multiple rows with the same line number) is your fact table. Create a join between the two tables. In the model tab, you should see a line go from dim to fact and see a 1 by the dim table, a * by the fact table (indicating a 1 to M  join) and an arrow pointing at the fact table (which signifies that the dim table will filter the fact table. Next, create a measure for the length like this Length = SUM('YourFactTableName'[Length]). Add a table visual (or whatever viz you want) and put in the line number from your dim table and the measure you created.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

4 REPLIES 4
audreygerred
Super User
Super User

Hi! Your first table, the one with name and line number (line number being unique) is your dimension table. Your second table (the one with multiple rows with the same line number) is your fact table. Create a join between the two tables. In the model tab, you should see a line go from dim to fact and see a 1 by the dim table, a * by the fact table (indicating a 1 to M  join) and an arrow pointing at the fact table (which signifies that the dim table will filter the fact table. Next, create a measure for the length like this Length = SUM('YourFactTableName'[Length]). Add a table visual (or whatever viz you want) and put in the line number from your dim table and the measure you created.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Thank you so uch for the help and the quick reply - worked a treat.

Awesome! You are very welcome - happy to help!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





BarryFletcher
Helper II
Helper II

The result should be

 

Line number 12345 = 3

Line number 45678 = 8

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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