Data model to make a visual from two fact tables

[Anonymous]

Hello everyone,

I have 2 Fact tables that have the same composite primary keys (based on 5 columns) and are joined to the same dimensions. I have many measures created and I want to create a table visual in Power BI that fetches data from both facts. For example column 1 from the first fact and column 2 from the second fact.  But I noticed that some measures and other numeric fields return the same value, for example : 

col1  col 2

type1  500

type2 500

type3 500

Total 500

I know this is a modeling problem, should I merge both facts into one fact table since they have the same PKs ? which join method should I use inner, full outer ... ?

Thank you in advance ! 

[Anonymous]

Hi  @Anonymous ,

The output value of your measurement is the same, which may be related to the measure value. If you can, you can share your measure value

You can also take a look at the link for the relationship between tables:

https://docs.microsoft.com/zh-cn/power-bi/transform-model/desktop-relationships-understand

Best Regards,

Liu Yang

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

[MattAllington · Accepted solution]

You have choices. If the data in the 2 fact tables is like data, then appending is a good option. Eg, sales by day and budget by day. If the data is not like data, then 2 fact tables is a better option, eg sales by day and budget by month. If you keep 2 fact tables, you need a common dimension table and maybe bridge tables to join them. Do not join them directly. If there is a common key in both tables, create a new dim table containing all possible values of the key without duplication. Join both facts to the dim. Hide the key column in the fact tables and only ever use the key column from the dim table in your visuals

[Anonymous]

They both analyse somewhat similar data on a monthly & yearly basis. So which join option would you recommend ? I don't wanna lose data so I'm thinking about a full outer,, what do you think ?

For the bridge table I altready tried it out : I extracted the PK columns from both tables made a union and deleted all duplicates, I then created a unique ID which is basically a concatenated value from all 5 columns then linked both facts to that bridge, it didn't work 😞 I'm new to all this data thing so maybe there's something wrong in my process. Also creating a bridge table with millions of rows has made my Azure Analysis Services a bit slow in deployment.

[MattAllington]

It's had to say without seeing the details.