Forum Discussion

Helper III

1 year ago

Solved

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:

and table 2 would have data such as below:

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?

audreygerred
1 year ago
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.