What is the correct way to establish relationships between fact tables using degenerate Dimension?

[Anonymous]

Hello,

I want to ask about what should be the most approriate way to link fact tables using degenerate dimension.

Here is the tables. 3 data mart with 3 fact table and 2 dimension (1 of them is a degenerate dimension named "BookingKey" which lives inside those fact tables). These table has been heavily simplified, but the core structures are still.

Such paterns of questions should be answered:

- How many invoices and comments does Booking with ID = 1 have.

- How many what is the total booking detail amount and total number of invoices does Company with ID =  1 have.

I can get the correct answer by using these relationships just like the images above. The thing is, I want to ask if these relationships in the image above are legit.

As far as I know, one should not join fact tables together. And the image above really seems like I'm joining fact table, despite the fact that actually I'm joining them using the degenarate dimension called "BookingKey". So can somebody tell me if the relationships above are correct and suitable with Power BI? Or I should have to use other techniques to drill accross those data marts?

I have searched a lot but seems that all the examples out there are just about dealing with single data mart. So I don't know exactly what should be done in this situation.

[dax · Accepted solution]

Hi PBIaze,

There is no rule point out that we can't join the fact tables together. You could join they together based on your requirement. You could use relationship to get different aggregate value(get count of comment in Comment table, calculate sum amount of invonce in Invoice table ). Or you also could put all fact into the same table.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.