How to set up data model with 2 fact tables

[Anonymous]

Hi all,

I've been building a report about the backlog of our company.

This data model consists of one fact table (all current sales orderlines that are in backlog) and several dimension tables.

Now I'd like to connect our service level to the backlog.
In other words, if I have visualized the aggregated backlog on item group level or item level I'd like to see the corresponding service level that we achieved (YTD, last month etc).
To calculate the service level I have to use another fact table containing all closed sales lines on an item level.

How would I introduce this additional fact table?
If I make a relationship between the backlog and closes sales lines tables it would result in a many to many relationship which i've been told to avoid at all cost.

Any tips? 🙂

[v-xicai · Accepted solution]

Hi @Anonymous ,

The suggestions of tex628 and PaulDBrown are great.

If the data structure of backlog table is the same with closes sales lines table, you might use Append feature to combine the two tables into one table, see more about how to append: Append vs. Merge in Power BI and Power Query . While before using append feature, you might add custom column to distinguish and identify the backlog table and closes sales lines table , see setting like below.

If the data structure is different between the two tables , and you wouldn't like to create many to many relationship, then you can create intermediate table like DAX below. Finally, create many to one relationship between the two fact table and new table.

New calculated Table = UNION(DISTINCT(backlog[orderlines]), DISTINCT('closes sales lines'[orderlines]))

Best Regards,

Amy

Community Support Team _ Amy

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

[PaulDBrown]

@Anonymous 

The best way is to use "bridge tables" (Dim Tables, Lookup tables) which have fields common to both of your fact tables (Date, Item, Market, Team, Person....)

You then use the fields in these bridge tables in all your slicers, filters measures, visuals etc...

The first table you should create is a Date or Calendar table linked to your date fields in both your fact tables.

Here is an example (albeit this particular example doesn't actually have a date table - but it does have a month table since "month" is the lowest granularity in both the Sales and Forecast tables):

[tex628]

Is it possible for you to append the closed lines ontop of the open ones using a custom column to categorize open/closed?