Forum Discussion
Many-to_many relationship with dates
Hi everyone,
I am struggling with a problem I can't figure out how to solve. I have two tables, the first one containing the report of the activities executed in a specific date within a certain Service (see screenshot below):
The other table contains the description of each Service ID related to a specified date-range:
The two tables have a many-to-many relationship through the "Service ID" col. What I want to do is to insert a new calculated column in the first table in which I want to get the description associated to the correct date-range.
i.e. in line #1 I want to get the description of the Service executed on the 15th October 2020, that is "Service A (2020)", while for the line #4 I want to see the description "Service A (2021)", since the activity has been carried out in 2021.
Is there any way to achieve this? RELATED doesn't work with many-to-many relationships, I have tried to use LOOKUPVALUE but the issue comes when trying to compare a single date with a date-range.
Any help would be appreciated.
Thanks in advance.
- Hi amitchandak,thanks for your reply.Actually, trying to implement the solution you proposed, I figured out that the following formula was doing the whole process in only one step:Col = MAXX(FILTER(Table_2,Table_2[Start_date]<=Table_1[Date] && Table_2[End_date]>=Table_1[Date] && Table_2[Service ID]=Table_1[Service ID]),Table_2[Descr])Thanks again!
2 Replies
- amitchandak
Super User
AAA_PBI , Add new columns in Table 1
Minx(filter(Table2, Table2[Start Date] <= Table1[Date] && Table2[End Date] >= Table1[Date]),Table1[Start Date])
maxx(filter(Table2, Table2[Start Date] <= Table1[Date] && Table2[End Date] >= Table1[Date]),Table1[End Date])refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8- Hi amitchandak,thanks for your reply.Actually, trying to implement the solution you proposed, I figured out that the following formula was doing the whole process in only one step:Col = MAXX(FILTER(Table_2,Table_2[Start_date]<=Table_1[Date] && Table_2[End_date]>=Table_1[Date] && Table_2[Service ID]=Table_1[Service ID]),Table_2[Descr])Thanks again!
