Forum Discussion
Comparing Line Count in two different tables
Something wrong, It's type error. I have tested it using your table.
if you can share your pbix table, let me check it.
// 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] )
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.