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 

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

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