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,
    I ran DAX Query View and found that the data is being assigned incorrectly according to the date from table2.

     

    In the common table I see this:

     



    However, this row from table2 should have been used in the given date:



    Thank you

    Ondřej

    • johnt75's avatar
      johnt75
      Super User
      1 year ago

      Those dates look like they are stored as strings. Use Power Query to turn them into date type.

      • Fidzi8's avatar
        Fidzi8
        Helper V
        1 year ago

        Thank you 👍, switching the column format to date helped.

        Ondřej 

  • 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