cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GVTionale
Helper II
Helper II

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"

5 REPLIES 5
amitchandak
Super User
Super User

@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



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

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

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

 

@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"
)

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.