Inserting the correct value

[Fidzi8]

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

[johnt75 · Accepted solution]

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

[v-karpurapud]

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

[johnt75]

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]

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 · Accepted solution]

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]

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

Ondřej

[Fidzi8]

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]

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

[Fidzi8]

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

Ondřej 

[johnt75 · Accepted solution]

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]

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