Help with FIFO logic with two Tables in DirectQuery

[Diaze2108]

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 the first delivery arrived is also the one that is unloaded first. 

I'm stuck with working in DQ and have to tables to do this- 

Transports Table and Unloading Table

Transports Table has the Columns LoadUnitId, Priority and ActualArrival Timestamp. 

Unloading Table has the column StarTime Unloading. 

Something like this: 

LoadUnitId	Priority	ActualArrival	StartTime Unloading
1234	1	01.01.2025 08:00	01.01.2025 08:15
4321	2	01.01.2025 07:35	01.01.2025 07:56
2134	1	01.01.2025 07:15	01.01.2025 08:19

What kind of expression can be used to check if a Load Unit that has the same priority, was delivered before another one, but unloaded later. 

I've been thinking of using Ranks but DirectQuery is a bit tricky and won't allow that. Maybe Flags would do the trick but I'm unsure how to use them in this case exactly. 

Any help would be highly appreciated. Thank you very much 🙂 

[Anonymous]

Hi @Diaze2108,

I wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If the community member's answer your query, please mark it as "Accept as Solution" and select "Yes" if it was helpful.
If you need any further assistance, feel free to reach out.

Please continue using Microsoft community forum.

Thank you,
Pavan.

[Anonymous]

Hi @Diaze2108,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept  as  Solution" and give a 'Kudos' so other members can easily find it.

Thank you,
Pavan.

[Anonymous]

Hi @Diaze2108,

Thank you for reaching out in Microsoft Community Forum.

Thank you  @bhanu_gautam for the helpful response.

As suggested by bhanu gautam, I hope this information was helpful. Please let me know if you have any further questions or you'd like to discuss this further. If this answers your question, please "Accept as Solution" and give it a 'Kudos' so others can find it easily.

Please continue using Microsoft community forum.

Regards,
Pavan.

[bhanu_gautam]

@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]

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]

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