Forum Discussion

Diaze2108's avatar
Diaze2108
Frequent Visitor
1 year ago

Help with FIFO logic with two Tables in DirectQuery

Hello,    I'm struggling with creating a way to measure and/or visualize if a Load Unit that has been delivered to us, was also unloaded first. Deliveries have Priority 1-3 so what we want is, that...
DAX
DirectQuery
FIFO
Filter
bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

Diaze2108 

Create two calculated columns to rank the load units based on ActualArrival and StartTime Unloading within each priority group.

 

RankArrival =
RANKX(
FILTER(CombinedTable, CombinedTable[Priority] = EARLIER(CombinedTable[Priority])),
CombinedTable[ActualArrival],
,
ASC,
DENSE
)

RankUnloading =
RANKX(
FILTER(CombinedTable, CombinedTable[Priority] = EARLIER(CombinedTable[Priority])),
CombinedTable[StartTime Unloading],
,
ASC,
DENSE
)

 

 

Create a calculated column to flag if the load unit that arrived first was also unloaded first.

IsFirstArrivedUnloadedFirst =
IF(
CombinedTable[RankArrival] = 1 && CombinedTable[RankUnloading] = 1,
TRUE,
FALSE
)

 

Add a table visual to your report.
Add the columns LoadUnitId, Priority, ActualArrival, StartTime Unloading, and IsFirstArrivedUnloadedFirst to the table

  • Diaze2108's avatar
    Diaze2108
    Frequent Visitor
    1 year ago

    Thanks for your quick reply! Unfortunately RANKX Functions are not usable in DirectQuery Models. I have no choice but to work within DQ constraints. 

    • bhanu_gautam's avatar
      bhanu_gautam
      Super User
      1 year ago

      Diaze2108 , Try using

      Create a calculated column to flag the earliest arrival within each priority group:

      EARLIEST_ARRIVAL_FLAG =
      IF(
      'Transports Table'[ActualArrival] =
      MINX(
      FILTER(
      'Transports Table',
      'Transports Table'[Priority] = EARLIER('Transports Table'[Priority])
      ),
      'Transports Table'[ActualArrival]
      ),
      1,
      0
      )

       

      Similarly Create a calculated column to flag the earliest unloading within each priority group:

      EARLIEST_UNLOADING_FLAG =
      IF(
      'Unloading Table'[StartTime Unloading] =
      MINX(
      FILTER(
      'Unloading Table',
      'Unloading Table'[Priority] = EARLIER('Unloading Table'[Priority])
      ),
      'Unloading Table'[StartTime Unloading]
      ),
      1,
      0
      )

       

      Combine the tables and create a final flag to check if the first arrived load unit was also the first unloaded:

      IS_FIRST_ARRIVED_UNLOADED_FIRST =
      IF(
      'Transports Table'[EARLIEST_ARRIVAL_FLAG] = 1 &&
      'Unloading Table'[EARLIEST_UNLOADING_FLAG] = 1,
      TRUE,
      FALSE
      )