Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I am on my first steps learning DAX and this is my first post.
I need to count the number of flights before an aircraft reaches a base. The expected result is on the last column below, as you can see, the aircraft flew 3 flights before reaching a base, then 1 and then 4 flights.
I think the best way would be counting the previous rows, based on date and aircraft nbr. And restart every time it reaches a base.
Tail Nbr | Time of Departure | Departure Airport | Arrival Airport | Base/Not Base | # of flights to reach a Base |
CXDR1 | 2020/01/03 10:39:00 | EEVC | EFEB | NotBase | |
CXDR1 | 2020/01/03 13:20:00 | EFEB | ELEF | NotBase | |
CXDR1 | 2020/01/03 15:59:00 | ELEF | EFMN | Base | 3 |
CXDR1 | 2020/01/04 11:27:00 | EFMN | EHAM | Base | 1 |
CXDR1 | 2020/01/04 14:37:00 | EHAM | EOWS | NotBase | |
CXDR1 | 2020/01/04 16:55:00 | EOWS | EHHK | NotBase | |
CXDR1 | 2020/01/05 08:00:00 | EHHK | EEML | NotBase | |
CXDR1 | 2020/01/05 12:35:00 | EEML | EFBH | Base | 4 |
CXDR1 | 2020/01/05 15:05:00 | EFBH | EFMH | Base | 1 |
SSLFE | 2020/01/01 14:44:00 | EHAM | EHHH | Base | 1 |
SSLFE | 2020/01/02 07:17:00 | EHHH | EEEX | NotBase | |
SSLFE | 2020/01/02 10:02:00 | EEEX | EPCS | NotBase | |
SSLFE | 2020/01/03 10:43:00 | EPCS | LMMN | NotBase | |
SSLFE | 2020/01/03 13:18:00 | LMMN | EBBR | NotBase | |
SSLFE | 2020/01/04 11:46:00 | EBBR | ELTE | Base | 5 |
SSLFE | 2020/01/05 13:15:00 | ELTE | EBBR | NotBase | |
SSLFE | 2020/01/05 17:40:00 | EBBR | ESLL | Base | 1 |
Thanks in advance,
Solved! Go to Solution.
Hi @CTozzi
I believe the value in the last row of your expected result should be 2 rather than 1
# of flights to reach a Base =
VAR previousbasetime_ =
CALCULATE (
MAX ( Table1[Time of Departure] );
ALLEXCEPT ( Table1; Table1[Tail Nbr] );
Table1[Base/Not Base] = "Base";
Table1[Time of Departure] < EARLIER ( Table1[Time of Departure] )
)
VAR previousNotbasetime_ =
IF (
ISBLANK ( previousbasetime_ );
CALCULATE (
MIN ( Table1[Time of Departure] );
ALLEXCEPT ( Table1; Table1[Tail Nbr] );
Table1[Base/Not Base] = "NotBase";
Table1[Time of Departure] < EARLIER ( Table1[Time of Departure] )
)
)
RETURN
IF (
Table1[Base/Not Base] = "Base";
IF (
ISBLANK ( previousbasetime_ );
CALCULATE (
COUNT ( Table1[Time of Departure] );
ALLEXCEPT ( Table1; Table1[Tail Nbr] );
Table1[Time of Departure] <= EARLIER ( Table1[Time of Departure] );
Table1[Time of Departure] >= previousNotbasetime_
);
CALCULATE (
COUNT ( Table1[Time of Departure] );
ALLEXCEPT ( Table1; Table1[Tail Nbr] );
Table1[Time of Departure] <= EARLIER ( Table1[Time of Departure] );
Table1[Time of Departure] > previousbasetime_
)
)
)
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs
Cheers
Hi @CTozzi
I believe the value in the last row of your expected result should be 2 rather than 1
# of flights to reach a Base =
VAR previousbasetime_ =
CALCULATE (
MAX ( Table1[Time of Departure] );
ALLEXCEPT ( Table1; Table1[Tail Nbr] );
Table1[Base/Not Base] = "Base";
Table1[Time of Departure] < EARLIER ( Table1[Time of Departure] )
)
VAR previousNotbasetime_ =
IF (
ISBLANK ( previousbasetime_ );
CALCULATE (
MIN ( Table1[Time of Departure] );
ALLEXCEPT ( Table1; Table1[Tail Nbr] );
Table1[Base/Not Base] = "NotBase";
Table1[Time of Departure] < EARLIER ( Table1[Time of Departure] )
)
)
RETURN
IF (
Table1[Base/Not Base] = "Base";
IF (
ISBLANK ( previousbasetime_ );
CALCULATE (
COUNT ( Table1[Time of Departure] );
ALLEXCEPT ( Table1; Table1[Tail Nbr] );
Table1[Time of Departure] <= EARLIER ( Table1[Time of Departure] );
Table1[Time of Departure] >= previousNotbasetime_
);
CALCULATE (
COUNT ( Table1[Time of Departure] );
ALLEXCEPT ( Table1; Table1[Tail Nbr] );
Table1[Time of Departure] <= EARLIER ( Table1[Time of Departure] );
Table1[Time of Departure] > previousbasetime_
)
)
)
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs
Cheers
Hi @CTozzi
Can you share the database? You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).
See if this version works better in terms of speed/resources consumption:
# of flights to reach a Base V2 =
VAR previousbasetime_ =
MAXX (
FILTER (
ALL ( Table1[Time of Departure], Table1[Tail Nbr], Table1[Base/Not Base] ),
Table1[Tail Nbr] = EARLIER ( Table1[Tail Nbr] )
&& Table1[Base/Not Base] = "Base"
&& Table1[Time of Departure] < EARLIER ( Table1[Time of Departure] )
),
Table1[Time of Departure]
)
VAR previousNotbasetime_ =
IF (
ISBLANK ( previousbasetime_ ),
MINX (
FILTER (
ALL ( Table1[Time of Departure], Table1[Tail Nbr], Table1[Base/Not Base] ),
Table1[Tail Nbr] = EARLIER ( Table1[Tail Nbr] )
&& Table1[Base/Not Base] = "NotBase"
&& Table1[Time of Departure] < EARLIER ( Table1[Time of Departure] )
),
Table1[Time of Departure]
)
)
RETURN
IF (
Table1[Base/Not Base] = "Base",
IF (
ISBLANK ( previousbasetime_ ),
COUNTROWS (
FILTER (
ALL ( Table1[Time of Departure], Table1[Tail Nbr] ),
Table1[Tail Nbr] = EARLIER ( Table1[Tail Nbr] )
&& Table1[Time of Departure] >= previousNotbasetime_
&& Table1[Time of Departure] <= EARLIER ( Table1[Time of Departure] )
)
),
COUNTROWS (
FILTER (
ALL ( Table1[Time of Departure], Table1[Tail Nbr] ),
Table1[Tail Nbr] = EARLIER ( Table1[Tail Nbr] )
&& Table1[Time of Departure] > previousbasetime_
&& Table1[Time of Departure] <= EARLIER ( Table1[Time of Departure] )
)
)
)
)
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |