Forum Discussion
Comparing Line Count in two different tables
Hi guys,
Still learning DAX, so not sure how I can do this.
I have a PO line table, where I have 8 items in an PO.
| UniqueID | item Name | Qty | PO # | line no | ||||
| PO001-a-1 | a | 1 | PO001 | 1 | ||||
| PO001-b-2 | b | 1 | PO001 | 2 | ||||
| PO001-c-3 | c | 1 | PO001 | 3 | ||||
| PO001-d-4 | d | 1 | PO001 | 4 | ||||
| PO001-e-5 | e | 1 | PO001 | 5 | ||||
| PO001-f-6 | f | 2 | PO001 | 6 | ||||
| PO001-g-7 | g | 1 | PO001 | 7 | ||||
| PO001-h-8 | h | 12 | PO001 | 8 |
I have another table, where I have a delivery Note, which has 7/8 items.
| UniqueID | item Name | DN # | Qty | PO # | line no | |||||
| PO001-a-1 | a | DN001 | 1 | PO001 | 1 | |||||
| PO001-b-2 | b | DN001 | 1 | PO001 | 2 | |||||
| PO001-c-3 | c | DN001 | 1 | PO001 | 3 | |||||
| PO001-d-4 | d | DN001 | 1 | PO001 | 4 | |||||
| PO001-e-5 | e | DN001 | 1 | PO001 | 5 | |||||
| PO001-f-6 | f | DN001 | 2 | PO001 | 6 | |||||
| PO001-g-7 | g | DN001 | 1 | PO001 | 7 |
How can I compare the no. of items in the two tables and identify if I have delivered the order in full or not ie all lines order have been fulfilled or not.
Something wrong, It's type error. I have tested it using your table.
if you can share your pbix table, let me check it.
9 Replies
it's 1 to many.
1 order line can be split delivered / partially delivered.// The selection of [PO #] must // be done from the Orders table, // never from Deliveries. All columns // that are in Orders and in Delivieries // at the same time should only be present // in Orders, so please remove them // from Deliveries (apart from the linking // column, of course). For instance, // Item Name, PO #, Line No should only live // in Orders. Numeric fields should never // be exposed to the user directly, only // through measures. // Orders[UniqueID] joins to // Deliveries[UniqueID] via // 1:* with one-way filtering. [# Items Ordered] = sum( Orders[Qty] ) [# Items Delivered] = sum( Deliveries[Qty] ) [Fullfilled] = ( [# Items Ordered] = [# Items Delivered] )
pratichi Hi, pratichi, if you want get the list of UniqueID which are not delivered (which don't exist in Delivery Note), you may use dax Except fuction to solve the proble. (https://docs.microsoft.com/en-us/dax/except-function-dax)
for your question,
Create a table,
NotDelivered = except( values('PO'[UniqueID], values('Delivery Note'[UniqueID])) whould return all the list of UniqueID which are not delivered.
Hope this helps you.
pratichi HI,
You may still use the Except function. Please try below to get the table of UniqueID which are not delivered as PO
notDelivered_Table =
EXCEPT(all(PO[UniqueID],PO[item Name],PO[Qty]),all(deliveryNote[UniqueID],deliveryNote[item Name],deliveryNote[Qty]))to get the number of ID, make a measure with countrows(notDelivered_Table).
Hope, this helps
