Linking tables based on between date

[MrMP]

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 matches and date is in between those two dates, it should create a relationship. Is there any good way to create this rather then writing DAX in table 1 since tables are quite large and might slow down the model.

Can Power Query perform faster?

Example:

-table 1

Date	Product ID	Sales
05-05-2021	P1	50
06-06-2021	P1	75
07-07-2021	P1	10

-table 2

Product ID	Date from	Date to	Attribute
P1	01-01-2021	31-10-2021	A1
P2	01-01-2021	31-12-2021	A2

Thank you!

[v-xiaotang]

Hi @MrMP 

M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDVBSIjAyNDJR2lABBhaqAUqwOUMNMFImQJc1OIhLkuECFLGAJ1xAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Product ID" = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product ID", type text}, {"Sales", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type","test",each Table.SelectRows(Table2,(tb2) => tb2[Date from]<= _[Date] and tb2[Date to] >= _[Date] and tb2[Product ID]=_[Product ID])),
    #"Expanded test" = Table.ExpandTableColumn(#"Added Custom", "test", {"Attribute"}, {"test.Attribute"})
in
    #"Expanded test"

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]

Thank you for your time. I have tried it and it still loads for a very long time. I have no idea how to proceed. Maybe move this to SQL before it enters power query.

[v-xiaotang]

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 · Accepted solution]

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

[smpa01]

@MrMP  can you use this measure

Measure =
CALCULATE (
    MAX ( Table2[Attribute] ),
    DATESBETWEEN (
        Table1[Date],
        MAX ( Table2[Date from] ),
        MAX ( Table2[Date to] )
    )
)

[MrMP]

Thank you. I also have to add table1.product id = table2.product id there somewhere and that is what I can solve now

[MrMP]

Should stay calculated column as it will be used for slicing later on.

[smpa01]

@MrMP  use this if you need a measure

Measure = 
CALCULATE (
    MAX ( Table2[Attribute] ),
    DATESBETWEEN (
        Table1[Date],
        MAX ( Table2[Date from] ),
        MAX ( Table2[Date to] )
    ),FILTER(Table2,Table2[Product ID]=MAX(Table1[Product ID]))
)

use this if you need a calculated column

Column =
CALCULATE (
    MAXX (
        FILTER (
            CROSSJOIN (
                Table1,
                SELECTCOLUMNS (
                    Table2,
                    "id", Table2[Product ID],
                    "from", Table2[Date from],
                    "to", Table2[Date to],
                    "attribute", Table2[Attribute]
                )
            ),
            [Product ID] = [id]
                && [Date] >= [from]
                && [Date] <= [to]
        ),
        [attribute]
    )
)

[MrMP]

Thank you. I still have problem with calculated column since it has been calculating for 15 mins now and I have to find another way.

[smpa01]

@MrMP  can you please try this to see if it is any better

Column =
CALCULATE (
    MAXX (
        FILTER (
            CROSSJOIN (
                SUMMARIZE ( Table1, Table1[Date], Table1[Product ID] ),
                SELECTCOLUMNS (
                    Table2,
                    "id", Table2[Product ID],
                    "from", Table2[Date from],
                    "to", Table2[Date to],
                    "attribute", Table2[Attribute]
                )
            ),
            [Product ID] = [id]
                && [Date] >= [from]
                && [Date] <= [to]
        ),
        [attribute]
    )
)

[MrMP]

Unfortunately no. It has been calculating for last 30 mins.

[amitchandak]

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

[MrMP]

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.