Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
I've started by doing the following:
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
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:
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?
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?
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.
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.
HI
I made a calculated coluns in your table
Here is the code; for me it is ok just check and advise