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_NUMBER CUSTOMER 101/2020 GABRIEL 120/2020 ADRIEN 124/2020 DIANA   ORDER DETAILS ORDER_NUMBER CUSTOMER ITEM_ID ORDER QTY SHIPPED QTY ...
Need Help
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

        Thanks for you response. couple of queries -

         

        (1)in your DAX where are you summing up the quantities of items in the details to determine if the document is shipped or pending etc.?

        (2) the formula i would like to apply is -

        if sum of balance qty for the doc<=0 status= shipped

        else

        if sum of balance qty for the doc>0 and shipped qty>0, partially shipped

        else

        status=pending

         

        I tried applying sumx to the dax u sent me but the result is wrong

         

        regards

         

         

         

        Sorry I am new and self learner of Power BI and hence will need further help as i got erroneous results

         

        regards

         

         

  • 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