Forum Discussion

emarc1's avatar
emarc1
Advocate II
8 years ago

Order / invoice data modelling

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 hea...
Need Help
emarc1's avatar
emarc1
Advocate II
8 years ago

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?

  • emarc1's avatar
    emarc1
    Advocate II
    8 years ago

    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's avatar
      emarc1
      Advocate II
      8 years ago

      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's avatar
        emarc1
        Advocate II
        8 years ago

        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.