Forum Discussion

MrMP's avatar
MrMP
Helper III
4 years ago
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...
Need Help
  • MrMP's avatar
    MrMP
    4 years ago

    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]
        )
    )

      

v-xiaotang's avatar
v-xiaotang
Community Support
4 years ago

Hi MrMP 

If you move it to SQL before ti enters power query, will it help? 

 

Best Regards,

Community Support Team _Tang

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

MrMP's avatar
MrMP
Helper III
4 years ago

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]
    )
)