topic Re: Comparing Line Count in two different tables in DAX Commands and Tips

Comparing Line Count in two different tables

pratichi — Tue, 27 Oct 2020 07:49:36 GMT

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.

Re: Comparing Line Count in two different tables

Anonymous — Tue, 27 Oct 2020 09:20:10 GMT

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.

Re: Comparing Line Count in two different tables

pratichi — Tue, 27 Oct 2020 10:17:07 GMT

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

Re: Comparing Line Count in two different tables

Anonymous — Tue, 27 Oct 2020 10:48:04 GMT

// 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] )

Re: Comparing Line Count in two different tables

colacan — Tue, 27 Oct 2020 11:31:40 GMT

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.

Re: Comparing Line Count in two different tables

pratichi — Tue, 27 Oct 2020 11:43:10 GMT

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.

Re: Comparing Line Count in two different tables

colacan — Tue, 27 Oct 2020 12:31:19 GMT

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

Re: Comparing Line Count in two different tables

pratichi — Thu, 29 Oct 2020 02:59:18 GMT

colacan I m getting this error?

Re: Comparing Line Count in two different tables

colacan — Thu, 29 Oct 2020 08:33:48 GMT

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

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

Re: Comparing Line Count in two different tables

pratichi — Fri, 30 Oct 2020 07:31:57 GMT

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.