Determine Delayed Arrivals by Responsible

[Syndicate_Admin]

I have the following table of facts with incidents that may or may not cause delays in the arrival of trains:

co_inc; co_tipo_inc; fe_inc; co_tren; bo_retraso; ca_retraso; ca_clientes
1; 3; 02/01/2024; 3; 1; 40; 30
1; 3; 03/01/2024; 1; 1; 25; 90
1; 3; 04/01/2024; 2; 0; 5; 35
1; 3; 04/01/2024; 5; 0; 10; 15
2; 5; 03/01/2024; 1; 0; 10; 35
3; 2; 04/01/2024; 4; 0; 15; 25
3; 2; 05/01/2024; 4; 1; 35; 60
4; 4; 02/01/2024; 3; 1; 65; 30
5; 1; 07/01/2024; 1; 0; 10; 15
5; 1; 07/01/2024; 3; 0; 0; 45
5; 1; 08/01/2024; 5; 1; 15; 40
5; 1; 08/01/2024; 3; 1; 0; 45
5; 1; 11/01/2024; 4; 1; 5; 45
5; 1; 12/01/2024; 2; 1; 10; 80
6; 3; 15/01/2024; 1; 1; 25; 55
6; 3; 16/01/2024; 2; 0; 5; 90
7; 5; 15/01/2024; 1; 1; 25; 35
7; 5; 15/01/2024; 2; 1; 65; 45
7; 5; 16/01/2024; 3; 0; 0; 35
8; 2; 16/01/2024; 5; 0; 5; 30
8; 2; 17/01/2024; 1; 0; 10; 90
9; 1; 23/01/2024; 5; 1; 35; 65
10; 4; 23/01/2024; 5; 1; 35; 80
10; 4; 24/01/2024; 4; 1; 10; 75
10; 4; 25/01/2024; 4; 0; 0; 70
10; 4; 25/01/2024; 3; 1; 5; 50
10; 4; 25/01/2024; 1; 1; 70; 15
10; 4; 26/01/2024; 4; 0; 0; 25
10; 4; 27/01/2024; 5; 0; 5; 65

co_inc: Incident Code

co_tipo_inc: Issue Type Code

fe_inc: Date of Occurrence

co_tren: Train Code

bo_retraso: Logical value indicating whether there was (1) or not (0) late arrival

ca_retraso: Minutes of delay caused by the incident

ca_clientes: Customers affected by the incident

I also have the following dimension table indicating which operator is responsible for each type of incident:

co_tipo_inc; en_operador
1; To
2; B
3; B
4; C
5; C

You would need to determine the number of late arrivals per operator knowing that, in case of duplication – i.e. if for the same train (co_tren) and on the same day (fe_inc) there were two (or more) different incidents (co_inc) responsible for two (or more) different operators (en_operador) resulting in late arrival (co_retraso = 1)-, The delay is attributed to the incident with the highest number of minutes of delay (ca_retraso) and, in the event of a tie, to the one with the highest number of passengers affected (ca_clientes).

Thanks in advance.

[lbendlin]

This should give you a starting point

You can add explicit measures as needed.

[Syndicate_Admin]

I've started by doing the following:

llegadas_retrasadas =

WERE retrasos_minutos =

FILTER(

facIncidencias,

facIncidencias[bo_retraso] = 1

&& facIncidencias[ca_retraso] =

CALCULATE(

MAX(facIncidencias[ca_retraso]),

ALLEXCEPT(

facIncidencias,

facIncidencias[co_tren],

facIncidencias[fe_inc]

)

)

)

WERE result = COUNTROWS(retrasos_minutos)

RETURN result

I'll stick with the queues that mean a late arrival and that, in addition, for each train/day pair, have the highest number of minutes of delay.

Now I would like to do something analogous to this second filter but with the highest number of travelers affected, to break ties when the number of minutes of delay is the same. However, Power BI won't let me create another variable where I can access the columns of the virtual table "retrasos_minutos"; Kind of:

VAR retrasos_clientes =
FILTER(
retrasos_minutos,
retrasos_minutos[ca_clientes] =
CALCULATE(
MAX(retrasos_minutos[ca_clientes]),
ALLEXCEPT(
retrasos_minutos,
retrasos_minutos[co_tren],
retrasos_minutos[fe_inc]
)
)
)

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Syndicate_Admin]

That the data can be presented in a matrix, or a stacked bar chart, such that:

Responsible for arrears

A 3

B 4

C 6

With something like this, I think I would achieve it:

llegadas_retrasadas =

VAR retrasos_minutos =

FILTER(

facIncidents,

facIncidents[bo_retraso] = 1

&& facIncidents[ca_retraso] =

CALCULATE(

MAX(facIncidents[ca_retraso]),

ALLEXCEPT(

facIncidents,

facIncidents[co_tren],

facIncidents[fe_inc]

)

)

)

VAR retrasos_clientes =

FILTER(

retrasos_minutos,

retrasos_minutos[ca_clientes] =

CALCULATE(

MAX(retrasos_minutos[ca_clientes]),

ALLEXCEPT(

retrasos_minutos,

retrasos_minutos[co_tren],

retrasos_minutos[fe_inc]

)

)

)

VAR result = COUNTROWS(retrasos_clientes)

RETURN result

But PBI doesn't let me access the columns of the virtual table contained in the "retrasos_minutos" variable from the "retrasos_clientes" variable.

I need to check, by train and day, which incident suffers the greatest delay in minutes, and if there are ties, check which one affects more passengers; That's why I can't do both checks at the same time, because it could be the case that an incident with a few minutes delay would affect a large number of customers.

PS: Is there no way to properly format the DAX code?

[Syndicate_Admin]

Going back to the main question, this code gives me the following error:

The table variable 'retrasos_minutos' cannot be used in the current context because a base table is expected.

Is there no way around this GDP limitation?

[lbendlin]

 Is there no way to properly format the DAX code?

You can use DAXFormatter.com, copy the HTML and paste it here.  May not come out perfectly but give it a try.  Sadly this forum still doesn't have DAX or M formatting capabilities.

[Syndicate_Admin]

Yes, I meant using a specific block:

<p>Hola</p>

I've tried to put the DAX code inside one of these but then it won't let me post the message.

[JamesFR06]

HI 

I made a calculated coluns in your table

Here is the code; for me it is ok just check and advise

Op Resp =

// linked to operator table to return the operator

VAR operator =

    MAXX ( RELATEDTABLE ( 'Del Arriv Op' ), 'Del Arriv Op'[ en_operador] ) //Storage of test values

VAR train = 'Del Arrival'[ co_tren]

VAR Dateinc = 'Del Arrival'[ fe_inc] //calculation of number of incident by date and by train

VAR nbincident =

    COUNTROWS (

        FILTER (

            'Del Arrival',

            'Del Arrival'[ fe_inc] = Dateinc

                && 'Del Arrival'[ co_tren] = train

                && 'Del Arrival'[ bo_retraso] = 1

        )

    ) //Calculation of max temp and max customers

VAR base =

    FILTER (

        'Del Arrival',

        'Del Arrival'[ fe_inc] = Dateinc

            && 'Del Arrival'[ co_tren] = train

            && 'Del Arrival'[ bo_retraso] = 1

    )

VAR MaxTemp =

    MAXX ( base, 'Del Arrival'[ ca_retraso] )

VAR MaxCust =

    MAXX ( base, 'Del Arrival'[ ca_clientes] ) //Calculation final result

VAR result =

    IF (

        nbincident = 1,

        operator,

        IF (

            nbincident > 1,

            IF (

                'Del Arrival'[ ca_retraso] = maxtemp,

                IF ( 'Del Arrival'[ ca_clientes] = MaxCust, operator )

            )

        )

    )

RETURN

    result

 Final result