Forum Discussion
Order / invoice data modelling
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
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?
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 YuI'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.
