Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

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.

View solution in original post

Resolver II
Resolver II

@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



@colacan I m getting this error?

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




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

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

Resolver II
Resolver II

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


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.


Not applicable

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.

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

Not applicable


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

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors