cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Measures in report causes unexpected row multiplication

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 IDORDER DATEPLANNED DELIVERY DATEORDER QTYTOTAL DELIVERED QTY
10009/07/202009/14/2020100
10109/07/202009/14/20202020
10209/07/202009/14/20205550
10309/07/202009/14/20203030
10409/07/202009/14/2020700
10509/07/202009/14/20202424



DELIVERY TABLE

ORDER IDACTUAL DELIVERY DATEPLANNED DELIVERY DATEDELIVERY PART NUMBERDELIVERED QTY
10109/10/202009/14/202018
10109/14/202009/14/2020212
10209/12/202009/14/2020150
10309/11/202009/14/2020130
10509/14/202009/14/2020124

 

Also I have a measure that gives the remaining quantity to be delivered: 

REMAINING_QTY = SUMX(Order,Order[ORDER QTY]-Order[TOTAL DELIVERED QUANTITY])

The problem is, when I put the following columns and the above mentioned measure into a table visualization, it goes bonkers and multiplies the rows for every 'ACTUAL DELIVERY DATE' value. Without the measure, the table looks fine. As soon as a simple measure is added, the table looks like below.

Could anyone help me understand what causes this?
 
ORDER IDORDER DATEACTUAL DELIVERY DATEORDER QTYTOTAL DELIVERED QTYDELIVERED QTYREMAINING_QTY
10009/07/202009/10/2020100 10
10009/07/202009/14/2020100 10
10009/07/202009/12/2020100 10
10009/07/202009/11/2020100 10
10109/07/202009/10/2020202080
10109/07/202009/14/20202020120
10109/07/202009/12/20202020 0
10109/07/202009/11/20202020 0
10209/07/202009/10/20205550 5
10209/07/202009/14/20205550 5
10209/07/202009/12/20205550505
10209/07/202009/11/20205550 5
10309/07/202009/10/20203030 0
10309/07/202009/14/20203030 0
10309/07/202009/12/20203030 0
10309/07/202009/11/20203030300
10409/07/202009/10/2020700 70
10409/07/202009/14/2020700 70
10409/07/202009/12/2020700 70
10409/07/202009/11/2020700 70
10509/07/202009/10/20202424 0
10509/07/202009/14/20202424240
10509/07/202009/12/20202424 0
10509/07/202009/11/20202424 0


Thank you very much in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi @jeromer ,

 

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.

 

Measure1.jpg

 

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.

View solution in original post

6 REPLIES 6
Highlighted
Super User IV
Super User IV

@jeromer , 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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

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!

Highlighted

@jeromer , 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?



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

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

Highlighted

Hi @jeromer ,

 

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.

 

Measure1.jpg

 

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.

View solution in original post

Highlighted

Hi @

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!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors