Simplified example: I have a 'hotel booking' fact table which holds the booking date and the stay date for each booking. And of course I have a shared dimension with all dates.
When I create two joins using the different columns for stay date and book date, I don't get the correct result, he only uses the first relationship, duplicates it. Also the label shows that it is in fact just using the first relationship.
According to me, this is basic functionality that should be available. I know that the workaround is to create a view for every alias of my dates dimension. But I'm not happy with that.
It is about joins not happening correctly. I was able to reproduce it on the sample database.
Step 1: alter data model for Sample Database in admin settings: re-label PRODUCT_ID in ORDERS table to Manager ID and use it as a second foreign key to the PEOPLE table.
Step 2: create a model on these tables. Start from Orders, join twice to People, first with User ID, second with Manager ID. Pick ID, User ID and Manager ID from Orders. Pick ID and Name from each People table.
At first sight the result looks OK except for the strange labels on the last two columns: I would have expected a reference to 'Manager' in there. But the data looks OK, so I could live with that.
I was starting to think that the issue I previously saw was maybe related to my postgreSQL database. So I was thinking about moving some sample data to a postgreSQL database and do the same test. But I went to dinner first.
And when I came back an hour later, the result had changed!!!! I first couldn't believe my eyes, but it was exactly the same as what I reported: only the first relationship gets used.