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