Reply
GVTionale
Helper II
Helper II
Partially syndicated - Outbound

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

Syndicated - Outbound

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Syndicated - Outbound

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

Syndicated - Outbound

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

 

Syndicated - Outbound

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

Syndicated - Outbound

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

 

 

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)