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

[m196804]

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 ID	No Items Issued	Date Issued
1	30	01-Dec-19
1	20	01-Jan-20
1	25	01-Feb-20
2	20	01-Dec-19
3	15	01-Dec-20
3	10	01-Jan-20
3	5	01-Fev-20
4	35	01-Dec-19

ITEMS RETURNED

Person Id	No Items Returned	No Items Lost	Date Returned
1	29	1	01-Jan-20
1	15	5	01-Feb-20
2	20	0	01-Jan-20
3	13	2	01-Jan-20
3	5	0	01-Feb-20

RESULTS

Person Id	No Items Issued	Date Issued	No Items Returned	No Items Lost	Date Returned
1	30	01-Dec-20	29	1	01-Jan-20
1	20	01-Jan-20	15	5	01-Feb-20
1	25	01-Feb-20
2	20	01-Dec-20	20	0	01-Jan-20
3	15	01-Dec-20	13	2	01-Jan-20
3	10	01-Jan-20	5	0	01-Feb-20
3	5	01-Feb-20
4	35	01-Dec-20

[ibarrau]

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.

[m196804]

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.

[ibarrau · 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.

[m196804]

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.