I have 3 tables:

• Fact Invoice Line
• Dim Date
• Fact Price Change

Fact Invoice Line is linked to date, with many lines per 1 date. Fact Price Change is linked to Fact Invoice Line using the primary key, so I said the relationship should be 1:1, because the relationship between Fact Price Change and Fact Invoice Line is indeed 1:1. The relationship is by default inactive.

I then made a metric where I active the relationship between Fact Price Change and Fact Invoice Line and sum something in Fact Price Change, specifying that the relationship between the two tables should be active.

When I say I want to have rows be something from Fact Price Change I get a table as I expect where each row is a Price Change Project and the sum of the price changes for that project. When I say I want rows to be years from the date of Fact Invoice Line I get the same total sum of all price changes across all time, and it says that is the total for each year, which is not what I want.

When I set the relationship to be 1 Fact Invoice Line to many Fact Price Change then re-do the test it works as expected.

Why does the 1:1 relationship not work as expected? I would expect the 1:1 relationship to allow me to group by any of the fields in Fact Invoice Line.