Forum Discussion

Fidzi8's avatar
Fidzi8
Helper V
1 year ago
Solved

Inserting the correct value

Hi comunnity, I need help with this problem. I have two simple tables. The first table contains a list of transports from where - to where (columns "C" - "D") on a given day (column „B“). ...
Need Help
Tips and Tricks
  • johnt75's avatar
    johnt75
    1 year ago

    You can create a calculated column in the first table like

    Distance =
SELECTCOLUMNS (
    FILTER (
        Table2,
        Table2[ID] = Table1[ID]
            && Table2[From] = Table1[From]
            && Table2[To] = Table2[To]
            && Table2[Valid From] <= Table1[Date]
            && (
                ISBLANK ( Table2[Valid To] )
                    || Table2[Valid To] >= Table1[Date]
            )
    ),
    "@value", Table2[Distance]
)
  • johnt75's avatar
    johnt75
    1 year ago

    Try

    Distance =
SELECTCOLUMNS (
    TOPN (
        1,
        FILTER (
            Table2,
            Table2[ID] = Table1[ID]
                && Table2[From] = Table1[From]
                && Table2[To] = Table2[To]
                && (
                    ( Table2[Valid From] <= Table1[Date]
                        && Table2[Valid To] >= Table1[Date] )
                        || ( ISBLANK ( Table2[Valid To] ) && ISBLANK ( Table2[Valid From] ) )
                )
        ),
        Table2[Value To], DESC
    ),
    "@value", Table2[Distance]
)
johnt75's avatar
johnt75
Super User
1 year ago

You can create a calculated column in the first table like

Distance =
SELECTCOLUMNS (
    FILTER (
        Table2,
        Table2[ID] = Table1[ID]
            && Table2[From] = Table1[From]
            && Table2[To] = Table2[To]
            && Table2[Valid From] <= Table1[Date]
            && (
                ISBLANK ( Table2[Valid To] )
                    || Table2[Valid To] >= Table1[Date]
            )
    ),
    "@value", Table2[Distance]
)
  • Fidzi8's avatar
    Fidzi8
    Helper V
    1 year ago

    Thank you Johnt75

    When I adjust the distance to the destination in a given period, like this:

     




    An error is displayed. If I leave the distances as in the previous case, 20km was incorrectly added everywhere, on some days it should have been 38km.

     




    It tells me that a table containing multiple values ​​was entered but only one value was expected.

    But at first glance I don't see that I have any duplication anywhere. The dates in my mileage table don't overlap but follow each other.

    Ondřej