Search code examples
dax

Is it possible to lookupvalue in PowerBi based on multiple criteria

I have the below two tables:

"Trip Data"

enter image description here

"IVMS Data"

enter image description here

I need to pick up the "Enter Time" value from "IVMS Data" which corresponds to the correct line in "Trip Data" and add it as a column to that table.

3 items need to match:

  • Truck
  • Load Location/Location
  • Load Time/Enter Time

The challenge I am facing is that the Load Time will never match the Enter Time, but it will be within the Enter and Exit Time values.

Is this something I can achieve in PBI/DAX or do I need to do this in another language and then pull it into PBI to report on it?

The end game is to pull the Trip, Enter Time and Exit Time all across to "Trip Data" but I figure that I can re-use this solution for all three of these ele

Solution

  • Using power query language, I would define a function-query named fEnterTimeFromTripRecord that, given a row from #"Trip Data", would return the enter time. I expect the following would work...

    let
  // Stage this data up front instead of redoing it each time the function is called
  Grouped = Table.Group(
    #"IVMS Data",
    {"Truck", "Location"},
    {
      "Times",
      // table of enter and exit times for the given truck and location
      each Table.SelectColumns(
        _,
        {"Enter Time", "Exit Time"}
      )
    }
  )
in
  each let
    LoadTime = [Load Time],
    Times = Grouped{[
      Truck = [Truck],
      Location = [Load Location]
    ]}[Times]
    Filtered = Table.SelectRows(
      Times,
      each [Enter Time] <= LoadTime and [Exit Time] >= LoadTime
    ),
    EnterTimes = Filtered[Enter Time],
    // There should be exactly one row in Filtered,
    // therefore one element in EnterTimes.
    // Otherwise, we have a problem and should throw an error!
    EnterTime = List.Single(EnterTimes)
  in
    EnterTime

    Once you debug that and you have a function that takes a row from the #"Trip Data" table and returns the enter time, the rest is easy.

    let
  Source = #"Trip Data",
  #"Added Enter Time" = Table.AddColumn(
    Source,
    "Enter Time",
    fEnterTimeFromTripRecord,
    type datetime
  )
in
  #"Added Enter Time"