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

Open DAX Query View and run

EVALUATE
GENERATE (
    Table1,
    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]
            )
    )
)
ORDER BY Table1[Delivery]

That will show you for every row in Table1 all the rows which would be returned from Table2. That should help you identify why multiple rows are being returned.

Fidzi8's avatar
Fidzi8
Helper V
1 year ago

Hi johnt75, 
could i ask you to modify the formula? I had to adjust the logic in Table2 a little.

Newly, the date will not be filled in for standard kilometers in the mileage records (columns "E" and "F").
In the period of non-standard kilometers, the date will be filled in.

Will it be possible to add kilometers according to the date of the transport as follows?:

1. If it is in the date period, the given km is selected.
2. If it is outside the date period, the km without date (standard kilometers) is selected.

Thanks a lot for your help.

Ondřej





  • johnt75's avatar
    johnt75
    Super User
    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]
)
    • Fidzi8's avatar
      Fidzi8
      Helper V
      1 year ago

      Hi johnt75, 
      works exactly as I need it to. Thank you very much. 👏

      Ondřej