Forum Discussion
Solved
Linking tables based on between date
Hi everyone, I am trying to link two tables with relationship. Table 1 should be connected using the Date and Product with table 2 where I have Product ID and between dates. So if Product ID matc...
I have managed to solve it with dax, and it calculates it in like 10sec. Doesnt look nice, but it does the trick.
Calculated Column =
CALCULATE (
FIRSTNONBLANK ( tab2[Attribute], tab2[Attribute] ),
FILTER (
tab2,
tab1[Product ID] = tab2[Product ID]
&& tab1[Date] >= tab2[DateFrom]
&& tab2[Date] <= tab2[Date To]
)
)
MrMP , if you only need attributes from table 2, then try a new column in table 1. It should support this on table with few million rows
New column in table 1 = maxx(filter(Table2, Table1[date] >= table2[from Date] && Table1[Date] <= Table2[To date]) , Table2[Attribute])
Thank you for help. I had to add table1.product id = table2.product id also there, but it keeps calculating for last 10mins. table 1 10mil rows, table 2 1mil rows.