- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 | BALANCE QTY |
101/2020 | GABRIEL | 12345 | 100 | 100 | |
120/2020 | ADRIEN | 45678 | 100 | 40 | 60 |
120/2020 | ADRIEN | 87654 | 60 | 60 | 0 |
124/2020 | DIANA | 97654 | 200 | 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"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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"
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
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.
Subject | Author | Posted | |
---|---|---|---|
07-05-2024 01:17 AM | |||
07-10-2024 08:08 AM | |||
04-10-2024 06:06 AM | |||
08-02-2024 11:49 AM | |||
04-11-2024 11:15 AM |
User | Count |
---|---|
121 | |
75 | |
46 | |
44 | |
35 |
User | Count |
---|---|
180 | |
85 | |
68 | |
47 | |
46 |