Need help with datamodel

[Velafyko]

Hi all

I'm struggeling to create a good data model to use in Power BI. I wonder if anyone can suggest a good soloution to this problem?

The original data source has a structure like this:

Ideally I would prefer to reduce this to a small data model, maybe just one table like this:

However, this does not work. The problem is that the data is extremly imperfekt. There are several problems:

1. I want to join invoice line with order line, so I can compare order and invoice price. But not all invoices have any data in the invoice line table

2 If there is no data in the invoice line table, I want to join the headers instead, so I can still compare invoiced and ordered amount.

3. Not all invoices have an order number

4. Not all orders have a corresponding invoice

5. Order place and invoice place is usually, but not always, the same. Sometimes they put the wrong place on the order, and then it's corrected on the invoice.

There are probably more problems too...

Any ideas?

[Velafyko]

Thanks. Setting up the data model like this works.

[amitchandak · Accepted solution]

@Velafyko , You need have following fact

1 - Order Header (Optional)

2 -Order Details - (Order Header + Order Line)  // This will  not join with order header

3. Invoice

4. Invoice Details (Invoice + invoice Place line _ invoice Line). This will with Invoice

These facts will not join with each other 

Create common dimension like

1. Date

2. Part /material

3. Order Number , or you can use one more copy of order header as dimesion

Join dimensions with fact 1-M

refer: https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.