Forum Discussion
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“). ...
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] )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] )
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.
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