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

Adding Multiple columns based on the maximum value of a column in another table

Hi everyone,

I'm pretty new to DAX and I can't seem to find a solution which covers exactly what I am trying to achieve. 

I have 2 calculated tables as below, with a many to many relationship which I need to join as shown in the RESULTS table. 

 

ITEM ISSUES lists the number of items  issued to each person on a particular date.

ITEM RETURNS  shows the number of items returned, the number lost and the date they were returned

 

I need to join the tables based on the next return date after the current issue date for each person/item issue date combination.

So for example, where Person 1 has an item issue date of 01-Dec-19  I need items returned, items lost and the return date  from ITEM RETURNS where the corresponding return date is the earliest date which is >= issue date, i.e. 01-Jan-20

 

Thanks for any help

 

ITEM ISSUES

Person IDNo Items IssuedDate Issued
13001-Dec-19
12001-Jan-20
12501-Feb-20
22001-Dec-19
31501-Dec-20
31001-Jan-20
3501-Fev-20
43501-Dec-19

 

ITEMS RETURNED

Person IdNo Items ReturnedNo Items LostDate Returned
129101-Jan-20
115501-Feb-20
220001-Jan-20
313201-Jan-20
35001-Feb-20

 

RESULTS

 

Person IdNo Items IssuedDate IssuedNo Items ReturnedNo Items LostDate Returned
13001-Dec-2029101-Jan-20
12001-Jan-2015501-Feb-20
125

01-Feb-20

   
22001-Dec-2020001-Jan-20
31501-Dec-2013201-Jan-20
31001-Jan-205001-Feb-20
3501-Feb-20   
43501-Dec-20   
      
1 ACCEPTED SOLUTION

Hey, if you can do it on SQL go ahead. There is nothing fater that a database engine for joining tables.

 

If you wanto to do it in edit queries. There is a button to do it. Check this links:

https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data#combine-queries

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

 

Consider adding a column of the combination of personid and date in order to get a unique column to do the merge. You can check how the transformation is done here:

https://youtu.be/K80MdUjyPEI?t=47

 

Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

View solution in original post

4 REPLIES 4
ibarrau
Super User
Super User

Hi there. This is a Data Model issue. Please consider reading about star schema.

 

You have two ways to fix this that I would recommend.

1- In case a person has a No. items issued, returned and lost by date, then merge this tables together by person and date in one table in edit queries. With power query you should congregate an unique table with people id dates and calculations. That sould be your fact table. This way you have only one table with results and this should be on Power Query and not in DAX.

 

2- Another solution would be creating a dimension (table) for date (calendar like this) and for persons with the unique ids. Then you won't relate both tables as  many to many. You will related both tables to DateTable and PersonsTable. Then any visualization you do with Person or Date with a value of No.Items will relate automatically.

 

I hope have described the point correctly. Otherwise ask again.

Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Thank you for your reply @ibarrau

Please could you explain further how to merge the tables together in Power Query?

If I was to do this in SQL I would just join on the Person ID and use a subquery to get the earliest ITEMS RETURNED[Date Returend] that is greater than the ITEM ISSUES[Date Issued] but I can't figure out how to do this within Power BI.

Hey, if you can do it on SQL go ahead. There is nothing fater that a database engine for joining tables.

 

If you wanto to do it in edit queries. There is a button to do it. Check this links:

https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data#combine-queries

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

 

Consider adding a column of the combination of personid and date in order to get a unique column to do the merge. You can check how the transformation is done here:

https://youtu.be/K80MdUjyPEI?t=47

 

Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Hi @ibarrau 

Thank you for your help.

Unfortunately my data sources are flat files so I can't use SQL but following your suggestion, I created a unique key in Power Query on the Person ID and dates which works perfectly.

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.