Forum Discussion
Resolver IIIProblem with many to many lookup
I am having trouble creating a filter column based on a calcuation in another table. It is something I have done several times, but it just isn't working here.
Here is the basic structure of the reference table.
| ID | End_Date | Filter-2 |
| 143 | 19.02.22 00:00 | UNKNOWN |
| 448 | 10.10.14 00:00 | NO |
| 448 | 21.03.19 00:00 | NO |
| 1369 | 23.07.14 00:00 | UNKNOWN |
| 36464 | 30.09.19 00:00 | UNKNOWN |
| 36464 | 19.02.22 00:00 | NO |
| 36464 | 30.09.20 00:00 | NO |
And here is the table where I need to create a filter column.
| ID | Date | Filter-1 | Filter-2 |
| 143 | 14.07.15 00:00 | L3 | 19.02.22 00:00 |
| 143 | 26.11.14 00:00 | L3 | 19.02.22 00:00 |
| 143 | 08.11.11 00:00 | L3 | |
| 143 | 29.11.11 00:00 | L3 | |
| 143 | 08.05.15 00:00 | L3 | 19.02.22 00:00 |
| 143 | 11.04.11 00:00 | L2 | |
| 143 | 09.05.12 00:00 | L3 | |
| 143 | 27.11.14 00:00 | L2 | |
| 143 | 05.04.12 00:00 | L3 | |
| 143 | 26.11.13 00:00 | L3 | 19.02.22 00:00 |
| 448 | 11.04.11 00:00 | L3 | |
| 448 | 11.04.11 00:00 | L2 | |
| 448 | 21.08.14 00:00 | L2 | |
| 448 | 24.11.15 00:00 | L3 | |
| 448 | 15.07.14 00:00 | L2 | |
| 448 | 15.07.14 00:00 | L3 | |
| 448 | 21.08.14 00:00 | L3 | |
| 1369 | 08.11.11 00:00 | L3 | |
| 1369 | 29.11.11 00:00 | L3 | 23.07.14 00:00 |
| 1369 | 08.05.12 00:00 | L2 | 23.07.14 00:00 |
| 1369 | 04.04.12 00:00 | L3 | 23.07.14 00:00 |
| 36464 | 14.01.21 00:00 | L3 | |
| 36464 | 04.10.17 00:00 | L3 | |
| 36464 | 10.01.20 00:00 | L3 | |
| 36464 | 20.02.21 00:00 | L3 | |
| 36464 | 04.10.18 00:00 | L3 | 30.09.19 00:00 |
| 36464 | 02.10.20 00:00 | L3 | |
| 36464 | 04.06.21 00:00 | L3 | |
| 36464 | 27.02.18 00:00 | L2 | 30.09.19 00:00 |
| 36464 | 08.06.20 00:00 | L3 | |
| 36464 | 05.06.19 00:00 | L3 | 30.09.19 00:00 |
| 36464 | 28.09.21 00:00 | L3 | |
| 36464 | 14.01.22 00:00 | L1 | 19.02.22 00:00 |
| 36464 | 04.10.18 00:00 | L2 | 30.09.19 00:00 |
| 36464 | 15.01.18 00:00 | L3 | |
| 36464 | 20.04.17 00:00 | L2 |
As you can see, I had no problem creating the Filter-1 column (from a third table), using basically the same strategy. But the Filter-2 column has multiple blanks. I have played around with it in different ways (e.g., MIN(), MAX(), etc.), and it does change which rows return the correct value, but there are always several blanks.
Here is the code I am using:
Filter-2 =
Var End_Date = CALCULATE(MIN('Table1'[End_Date]), FILTER('Table1',
'Table1'[ID] = 'Table2'[ID] &&
'Table1'[End_Date] >= 'Table2'[Date])
)
Var Filter_2 = CALCULATE(SELECTEDVALUE('Table1'[Filter-2]), FILTER(
'Table1',
'Table1'[ID] = 'Table2'[ID] &&
'Table1'[End Date] = End_Date)
)
RETURN Filter_2
Any ideas?
18 Replies
MJEnnisNote that in the second table above, I was testing to see if the correct end_date was being returned. The correct Filter_2 is returned in the exact same cells.
- tamerj1
Community Champion
It is the context transition. CALCULATE converts the row into a filter. If the date in table 2 is missing in Table 1 then the date in the filter context is empty and CALCULATE returns blank.
- MJEnnis
All rows in Table 1 have an End Date. And the VAR End_Date calculated in the calculated column of Table 2 matches the date in Table1 (as seen in the copied entries above). It is just that CALCULATE is retrieving the match for some rows in Table 2 and not for others. If I replace SELECTEDVALUE with MIN or MAX, then it retrieves the correct date for different rows. I think for some reason CALCULATE(MIN('Table1'[End_Date])... is not working the way I expect it too.
- v-chenwuz-msft
Community Support
Hi MJEnnis ,
We do not use selectedvalue when create column but [column name] directly. And if we want get filter result form table1 where table1 id = table2 id, we need to use eariler() function.
So please try the following code to get the min date depend on ID from table 1:
Filter-2 = CALCULATE( MIN( 'Table1'[End_Date] ), FILTER( 'Table1', 'Table1'[ID] = EARLIER( 'Table2'[ID] ) && 'Table1'[End_Date] >= EARLIER( 'Table2'[Date] ) ) )Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- MJEnnis
Thanks a lot for the suggestion. The code you propose doesn't really produce the result I have explained above. Moreover, I managed to get it to work with SELECTEDVALUE() and without EARLIER(). Will see about using the column name directly in the future.
