Forum Discussion

pratichi's avatar
pratichi
Frequent Visitor
5 years ago
Solved

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 ...
DAX Calculation
Distinct Counts
  • colacan's avatar
    colacan
    5 years ago

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

     

     

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago
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's avatar
    pratichi
    Frequent Visitor
    5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

       

      // 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's avatar
        pratichi
        Frequent Visitor
        5 years ago

        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.