cancel
Showing results 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.

1 ACCEPTED SOLUTION
Resolver II

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 9
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

Frequent Visitor

@colacan I m getting this error?

Resolver II

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

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

Frequent Visitor

@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

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

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
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.
Frequent Visitor

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

Anonymous
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] )``````

Frequent Visitor

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.

Announcements

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors