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“).

 


The second table contains kilometers (column "D") valid in a given period (columns "E" - "F") for a given destination (columns "B" - "C").
Different kilometers on different dates are due to the detour.



I need to upload the correct kilometers for a given date and destination into the first table.


Thank you Ondřej

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

10 Replies

  • 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

  • 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

      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]
)
  • v-karpurapud's avatar
    v-karpurapud
    Community Support
    1 year ago

    Hi Fidzi8 

    Could you please confirm if your query have been resolved the solution provided by johnt75 ? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

    Thank you