Forum Discussion
Conslidate based upon two unique ids
Not sure if this is what you had in mind, but you can group by PO and Line and get the Max of Qty Ordered and the Sum of Rec'd:
Hey Nick,
Thanks for that! That is solving the issue I was having, but unfortunately another one has come up. Here's where I stand:
I had to create a separate data table (reference) from the original one, because it wouldn't let me add another column to the original data source and I needed the other data that wasn't included in the grouping. So, I did so and I have it working, but when I create slicers to try and filter the new grouped data I am having some issues.
We have multiple dates listed on the original data (Date they told us it would arrive and the date it actually arrived), and I need it to filter the POs by the date that it arrived. Unfortunately, it seems that when I connect the two data tables, it automatically connects it with the date that they told us it was going to arrive that is in the original data source. Is there a way to change this?
Thanks!
Hey,
I am assuming your table is set up as follows, using PO -> PO as a relationship.
In that case, click on your slicer and ensure that you have Arrival Date in your field box, rather than Scheduled Date.
Please let me know if your setup is different than this.
Alex
Hi Alex,
Thanks again for your help with this. Yes, I have the relationship set up as PO # --> PO #. Also, I do have the field set up to the received date and it seems to work for most, but not all of the POs.
In the example below, PO 219702, line 2, was received in on 8/20/2018, but when I filter it by the month and year (two separate slicers), it is including it with July. Not sure what is going on here...
Here is the raw data, as you can see line 1 of this PO should be included, but line 2 should not.
Hey,
Looking at your data, it appears there should have a relationship from PO# to PO# and Line# to Line#. How are you handling this? I suspect you will find an error in there somewhere because Power BI doesn't support simultaneous relationships to my knowledge.
Further to this, you must have more than one date for each PO and Line combination because there is also a Delivery ID involved. From your raw data, I could expect to see different delivery dates for PO 1111, Line 2, Delivery 1244 and PO 1111, Line 2, Delivery 1255, for example.
Without seeing your complete data model I am making some assumptions here!
If your data was initially stored together, I would suggest using that. The diagrams in the image below could have been obtained using your raw data just as easily as after the grouping that you initially requested.
I understand this doesn't answer your question. If you can post a comprehensive data model, possibly in a pbix or pbit file with a small amount of dummy data, I would take a look at it for you.
Alex