Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have two tables; Order and Delivery. Not every order in the Order table has a record in Delivery table (some orders are never delivered) and the two tables are bound by Order Id column with one to many relationship with filter direction being from Order to Delivery. Tables are as below.
ORDER TABLE
ORDER ID | ORDER DATE | PLANNED DELIVERY DATE | ORDER QTY | TOTAL DELIVERED QTY |
100 | 09/07/2020 | 09/14/2020 | 10 | 0 |
101 | 09/07/2020 | 09/14/2020 | 20 | 20 |
102 | 09/07/2020 | 09/14/2020 | 55 | 50 |
103 | 09/07/2020 | 09/14/2020 | 30 | 30 |
104 | 09/07/2020 | 09/14/2020 | 70 | 0 |
105 | 09/07/2020 | 09/14/2020 | 24 | 24 |
DELIVERY TABLE
ORDER ID | ACTUAL DELIVERY DATE | PLANNED DELIVERY DATE | DELIVERY PART NUMBER | DELIVERED QTY |
101 | 09/10/2020 | 09/14/2020 | 1 | 8 |
101 | 09/14/2020 | 09/14/2020 | 2 | 12 |
102 | 09/12/2020 | 09/14/2020 | 1 | 50 |
103 | 09/11/2020 | 09/14/2020 | 1 | 30 |
105 | 09/14/2020 | 09/14/2020 | 1 | 24 |
Also I have a measure that gives the remaining quantity to be delivered:
ORDER ID | ORDER DATE | ACTUAL DELIVERY DATE | ORDER QTY | TOTAL DELIVERED QTY | DELIVERED QTY | REMAINING_QTY |
100 | 09/07/2020 | 09/10/2020 | 10 | 0 | 10 | |
100 | 09/07/2020 | 09/14/2020 | 10 | 0 | 10 | |
100 | 09/07/2020 | 09/12/2020 | 10 | 0 | 10 | |
100 | 09/07/2020 | 09/11/2020 | 10 | 0 | 10 | |
101 | 09/07/2020 | 09/10/2020 | 20 | 20 | 8 | 0 |
101 | 09/07/2020 | 09/14/2020 | 20 | 20 | 12 | 0 |
101 | 09/07/2020 | 09/12/2020 | 20 | 20 | 0 | |
101 | 09/07/2020 | 09/11/2020 | 20 | 20 | 0 | |
102 | 09/07/2020 | 09/10/2020 | 55 | 50 | 5 | |
102 | 09/07/2020 | 09/14/2020 | 55 | 50 | 5 | |
102 | 09/07/2020 | 09/12/2020 | 55 | 50 | 50 | 5 |
102 | 09/07/2020 | 09/11/2020 | 55 | 50 | 5 | |
103 | 09/07/2020 | 09/10/2020 | 30 | 30 | 0 | |
103 | 09/07/2020 | 09/14/2020 | 30 | 30 | 0 | |
103 | 09/07/2020 | 09/12/2020 | 30 | 30 | 0 | |
103 | 09/07/2020 | 09/11/2020 | 30 | 30 | 30 | 0 |
104 | 09/07/2020 | 09/10/2020 | 70 | 0 | 70 | |
104 | 09/07/2020 | 09/14/2020 | 70 | 0 | 70 | |
104 | 09/07/2020 | 09/12/2020 | 70 | 0 | 70 | |
104 | 09/07/2020 | 09/11/2020 | 70 | 0 | 70 | |
105 | 09/07/2020 | 09/10/2020 | 24 | 24 | 0 | |
105 | 09/07/2020 | 09/14/2020 | 24 | 24 | 24 | 0 |
105 | 09/07/2020 | 09/12/2020 | 24 | 24 | 0 | |
105 | 09/07/2020 | 09/11/2020 | 24 | 24 | 0 |
Thank you very much in advance!
Solved! Go to Solution.
Hi @Anonymous ,
Because the IDs do not match exactly, each of Order IDs will match all ACTUAL DELIVERY DATE.
Maybe you can create two table visuals. One shows the ID and actual delivery date, another shows the ID, order date, and remain qty.
Due to your table structure and you can only create Measure, the solution does have limitations.
Or you can add the undelivered order ID to the DELIVERY Table in the data source.
If you have any question, please kindly ask here and we will try to resolve it.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
@Anonymous , what I am getting is you are putting header and line together and it repeating for the delivery date,
Now you showing the remaining of the header to there not impact because of details
so have measure like
REMAINING_QTY = SUM(Order[ORDER QTY])-Sum(Delivery[TOTAL DELIVERED QUANTITY])
So either show max of the delivery date, or use measure like above
@Anonymous , if am suggesting to use Delivery[DELIVERED QTY]), and analyze.
REMAINING_QTY = SUM(Order[ORDER QTY])-Sum(Delivery[DELIVERED QTY])
Can you sample output in table format?
@amitchandak ,
REMAINING_QTY = SUM(Order[ORDER QTY])-Sum(Delivery[DELIVERED QTY]) ==> this formula is not relevant to what I want to show.
Unfortunately, I cannot manipulate tables or columns because I am working on an external database. I also cannot create calculated columns. I can only create measures.
Frankly, I am more interested in the cause of the problem than the solution. Because I have similar measures I'd like to add to the model.
Thanks!
Hi @Anonymous ,
Because the IDs do not match exactly, each of Order IDs will match all ACTUAL DELIVERY DATE.
Maybe you can create two table visuals. One shows the ID and actual delivery date, another shows the ID, order date, and remain qty.
Due to your table structure and you can only create Measure, the solution does have limitations.
Or you can add the undelivered order ID to the DELIVERY Table in the data source.
If you have any question, please kindly ask here and we will try to resolve it.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi @v-zhenbw-msftv,
I have scoured the internet for the cause of my problem and have found that it has something to do with auto-exist feature of DAX. It is not even about ID's not matching, because I tried that too.
Basically if columns of two different tables are put into a report, and when the report is subjected to a query, the hidden blank rows all become visible. I just had to accept that I had to show delivery and order columns in different reports like you suggested (which was also my go-to solution) even though it is not quite what was expected from me. I couldn't find a solution to it exactly, but I did find an explanation. For those who are curious about what causes this could check out the below link (couldn't figure out linking, sorry)
https://powerpivotpro.com/2017/08/autoexist-cross-table-filtering/
Anyway, I appreciate it that you took your time to respond to my post and wish everyone a nice day!
Hi @amitchandak, could you please elaborate on what you mean? What is the difference between
REMAINING_QTY = SUM(Order[ORDER QTY])-Sum(Order[TOTAL DELIVERED QUANTITY])
and
REMAINING_QTY = SUMX(Order,Order[ORDER QTY]-Order[TOTAL DELIVERED QUANTITY])
The 'TOTAL DELIVERED QUANTITY' column only exists in the Order table. So I believe the 2nd line below is the one you presented as solution. But unfortunately it does not work. The same problem persists.
REMAINING_QTY = SUM(Order[ORDER QTY])-Sum(Delivery[TOTAL DELIVERED QUANTITY])
REMAINING_QTY = SUM(Order[ORDER QTY])-Sum(Order[TOTAL DELIVERED QUANTITY])
Thank you!
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |