Forum Discussion

GVTionale's avatar
GVTionale
Icon for Helper II rankHelper II
6 years ago

column value in master table to be updated based on conditional sumx value in related table

I HAVE 2 TABLES
ORDER MASTER

ORDER_NUMBERCUSTOMER
101/2020GABRIEL
120/2020ADRIEN
124/2020DIANA

 

ORDER DETAILS

ORDER_NUMBERCUSTOMERITEM_IDORDER QTYSHIPPED QTYBALANCE QTY
101/2020GABRIEL12345100100 
120/2020ADRIEN456781004060
120/2020ADRIEN8765460600
124/2020DIANA97654200 200

 

 

PROBLEM :
I NEED TO DERIVE "SHIPMENT STATUS" FOR THE ORDER NUMBER AND UPDATE IN SHIPMENT STATUS COLUMN IN MASTER TABLE BASED ON FOLL. CONDITIONS - 

IF ALL ITEMS IN THE ORDER ARE FULLY SHIPPED, SHIPMENT STATUS ="SHIPPED"
IF SOME ITEMS ARE SHIPPED AND THERE IS BALANCE OF OTHERS, SHIPMENT STATUS="PARTIALLY SHIPPED"
IF NO SHIPMENT HAS BEEN MADE AND FULL ORDER QTY IS PENDING, SHIPMENT STATUS="PENDING"

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    GVTionale 

    You can create a new column in order master like this

    Balance = sumx(filter(ORDERDETAILS,ORDERDETAILS[ORDER_NUMBER]=ORDERMASTER[ORDER_NUMBER]),ORDERDETAILS[ORDER QTY]-ORDERDETAILS[SHIPPED QTY])

     

    Now you can use Switch true or If to create the status column

    • GVTionale's avatar
      GVTionale
      Icon for Helper II rankHelper II
      6 years ago

      Dear Amit

       

      Thanks for your response.

       

      is it possible to have only the Shipment Status column in the Master and update using a single If and filter dax mentioned by you, instead of one more column with balance qty?

       

      regards

       

      • v-chuncz-msft's avatar
        v-chuncz-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        GVTionale 

         

        You may refer to the DAX below.

        Column =
SWITCH (
    TRUE (),
    ISEMPTY (
        FILTER (
            RELATEDTABLE ( DETAILS ),
            DETAILS[BALANCE QTY] > 0
        )
    ), "SHIPPED",
    ISEMPTY (
        FILTER (
            RELATEDTABLE ( DETAILS ),
            DETAILS[SHIPPED QTY] > 0
        )
    ), "PENDING",
    "PARTIALLY SHIPPED"
)

         

    • GVTionale's avatar
      GVTionale
      Icon for Helper II rankHelper II
      6 years ago

      Hi

       

      i have already created a column in my details to calculate the balance qty.

      what i need now is the summed up result to reflect the position at document level to update the status in the master.

       

      if can get the status of partial shipment vs fully shipped as well it will be great (if shipped qty>0 and pending qty>0 it will be partially shipped)

       

      regards