Order / invoice data modelling

[emarc1]

I've always struggled with order and invoice tables. My data has four important fact tables:

Order line details

Order header

Invoice line details

Invoice header

Some of the data in the header tables is redundant but every table contains enough to be too useful to skip out. To make life easier... If I could denormalise these into one table it'd be wonderful but that seems more complicated in other ways. You have several hurdles:

• orders split over multiple invoices
• invoices with no order data (eg. refunds)
• actual order lines split over multiple invoices
• potentially multiple orders per invoices (though I don't think this occurs in my data)

I've settled on just denormalising into two tables:

Orders

Invoices

Working with just the invoices table is easy... Everything is finalised and easy to make relationships and measures from. Orders on the other hand is a pain. I'm not sure how I should relate this table to the others. Here's what I have been using recently:

Most of my work was done just using the invoices table previously but now I'm using that orders table more and more (calculating complete/incomplete orders, time to completion, etc). 

The problems arise when there isn't an invoice for an order. What is the best way to get around this? I started using LOOKUPVALUE to pull item and customer information... I've had to make all the filtering single direction though (it seems to interfere and I don't know why, I thought LOOKUPVALUE was meant to ignore relationships). I'm not sure if this is the best way.

[v-qiuyu-msft]

---

---

The problems arise when there isn't an invoice for an order. What is the best way to get around this? I started using LOOKUPVALUE to pull item and customer information... I've had to make all the filtering single direction though (it seems to interfere and I don't know why, I thought LOOKUPVALUE was meant to ignore relationships). I'm not sure if this is the best way.

Hi @emarc1,

If this is no invoice record for the order, which result you want? Can you clarify it? 

It would be better if you could share some sample data and show desired results for our analysis. 

Best Regards,
Qiuyun Yu 

[emarc1]

Thank you for your reply @v-qiuyu-msft.

Scenario:

• An order is placed and appears in the 'orders' table.
• The 'order' table contains the customer and item codes.
• This order has not yet been invoiced yet so there is not a line in the 'invoices' table with a related order number.
• Therefore, in this data model, there is no relationship between this row of the 'orders' table and the 'customers' and 'items' dimension tables. 

What is the best way to get the customer and item dimensions for this order? Is a better data model structure possible?

To get around this (for now), I have used LOOKUPVALUE but it doesn't work unless I turn off some bi-directional filtering. Is that normal? Why does LOOKUPVALUE get affected by filtering?

[v-qiuyu-msft]

Hi @emarc1,

You can follow these two links to do data modeling:

https://www.red-gate.com/simple-talk/sql/bi/power-bi-data-modelling/ 

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-create-and-manage-relationships/

Looupvalue() function should work well with both cross filter. If you don't turn off it, is there any error throws out when you use Lookupvalue() function? 

Best Regards,
Qiuyun Yu 

[emarc1]

I've been trying to perfect my data model for a long time now so I've been through quite a lot of guides. The guides usually only cover invoice data. That's easy! As you can see above, I've already merged the Invoice header and line tables into one within the query editor. I need to be able to work with the order data too. Most orders get invoiced within a few days but some may take more than a month. I need both orders and invoices for different tasks. It didn't seem possible to easily merge the order and invoice data into one table however. 

Currently, it seems to specifically be the relationship between the 'Invoices' and 'Items' that causes my lookupvalue functions in 'Orders' to have issues. In 'Orders' I have a calculated column:

LOOKUPVALUE(
    Items[Item Description],
    Items[Item Code], Orders[Item Code]
)

While the 'Invoices'<>'Items' relationship's cross filtering is set to BOTH, the lookupvalue function shows blank item descriptions on any order line that does not have a related invoice line. When the the cross filtering is set to SINGLE the lookupvalue works as expected and shows all of the item descriptions. There are no errors however the cross-filtering was useful as the various slicers (eg for date or customers) could filter the items in an item slicer but now they can't so now the item slicer contains a lot of redundant data as it's unfiltered.

[emarc1]

I've been chipping away at the Ferrari/Russo 'Analyzing Data with Microsoft Power BI and Power Pivot for Excel' book which has some advice on this. It suggests to use:

Customers <> Orders <> OrdersInvoicesBridge <> Invoices

Orders linked with the bridge via an Order Number & Line key.

Invoices linked with the bridge via an Order Numer & Line & Invoice Number key.

Quantities invoiced can be held in the bridge table.

As my company never puts multiple orders on a single invoice, I think I can actually skip out the bridge table so:

Items / Customers <> Orders <> Invoices

It doesn't solve my issue of Invoices without Orders but I think I will have to DAX my way around that issue. I will update this once I nail more issues down.

[emarc1]

I think I have finally come up with a solution to when there are invoices that do not have an order number attached... 

In short, the solution is to just append the important data from the invoices table into the orders table so data can then flow from the dimensions through that.

To do this:

• For the invoice table:
  
  • Fill the blank order number fields with the invoice number instead (and add a prefix so there's no possibility of a data clash).
• For the order table:
  
  • Make a new table with invoice data for just the invoices with no order number and add the essential columns for dimension table links (eg. customer and item codes, dates, etc) and the order number (with the same prefix as above).
  • Append this table to the orders table.

This doesn't affect any other data and allows filtering to flow properly. I think it'd also be possible with a bridge table but it's a rare occurence for us and it's possible that a bridge table could create larger performance issues.