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