Forum Discussion
Merge queries
Hello @gkakun ,
You have published this as "Combine Queries". Definitely want to do this in Power Query? It is feasible by a three-way search, but it is quite advanced M code and is not very effective in very large datasets.
This can be better adapted to doing so as a DAX measure, something like:
_transShift = VAR tTimestamp = MAX(transTable[Trans Timestamp]) RETURN CALCULATE( MAX(shiftTable([SHIFT]), FILTER( shiftTable, shiftTable[Start Time] <= tTimestamp && shiftTable[End Time] >= tTimestamp ) )
gkakun ,
If your tables are very large, this may not be very performant and may take a long time to load.
You can try adding it as a calculated column instead using amitchandak 's answer above (which didn't appear on my screen originally). Otherwise, we could try the Power Query lookup and see how that works out for you.
Pete
Looks like your DAX measure is partially working, i have many blank rows, still to understand why some dates not captured
Hi gkakun
If you want to build a calculated column like amitchandak 's reply, you don't need to build a relationship between two tables.
And I think there may be something wrong in your Start Time and End Time.
You see in my red box, Start Time seems to be later than End Time.
This may cause some dates not captured.
Please check your values.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.