Comparing Line Count in two different tables

[pratichi]

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.

[colacan]

@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

[pratichi]

@colacan I m getting this error?

[colacan · Accepted solution]

Something wrong, It's type error. I have tested it using your table. 

if you can share your pbix table, let me check it.

[pratichi]

@colacan Thanks it worked, but I found a better way using merge queries.
Merging the tables and expanding to pull DN no and Unique ID for each of the lines provided me the visibility.

[colacan]

@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.

[Anonymous]

What's the relationship between these tables? Is it 1-to-1 on UniqueID? If it's 1-to-1, then these tables should be consolidated into 1 table. If not, then please give us the model.

[pratichi]

it's 1 to many.
1 order line can be split delivered / partially delivered. 

[Anonymous]

// 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 @Anonymous ,

I am already able to check if each line item ( eg. item a) is delivered in full. 

I've done is by checking if for each unique id qty order = qty delivered. This is defining item delivered in Full or not in Full.
In case there is over delivery, we might have sitaution where total qty order = total qty delivered but we might not have delivered an item at all. 

What I want to check if if in order PO001 we have 8 line items (a-h) and in delivery, we only delivered 7 line items (a-g), I want to the formula to flag Order Completed or incomplete. technically, the system should check Order PO001 has 8 lines and then check the delivery table to see if all the 8 lines have been delivered/shipped out. 

If all items are delivered and all lines have qty order = qty delivered then order is Completed in Full.
if all items are delivered and not all lines have qty order = qty delivered then order is Completed but not in Full / Completed but in excess
if not all items are delivered but all the lines delivered have qty order = qty Delivered then order is Incomplete 

Hope it adds a bit more clarity. 
I'm trying to use
Line Order = calculate (distinctcount( 'PO Table '[unique-Id]), 'PO Table 'PO #) 
Line Delivered = Calculate (Distinctcount('DN Table' [unique-Id]), 'DN Table' DN #)

I'm getting absurb figures.. so not sure if the formula is correct.