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

      

MrMP's avatar
MrMP
Helper III
4 years ago

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

MrMP's avatar
MrMP
Helper III
4 years ago

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

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

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

      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's avatar
        smpa01
        Community Champion
        4 years ago

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