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