cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlanGroskreutz Frequent Visitor
Frequent Visitor

DAX - count inside group

Hi all,

I am pretty new to Power BI and have a good idea of what I want to do, but not the exact code to do it.  I am analysing airport data and want to know how often an aircraft is waiting to get into its parking position because the previous aircraft hasn't left yet.  The events are called inblocks for when it arrives and offblocks for when it leaves.  I have a table that lists, among other things,  the aircraft code (aircraftRegistration), the parking position (stand) the actual inblock time (AIBT), the actual offblock time (AOBT) and the scheduled inblock time (SIBT).

Here is a sample of the table with the referenced columns.  Notice that all rows inlcude SIBT, AIBT, and AOBT, and each row records an aircraft's trip through the airport, so it is a seperate arrival and departure.  I know I need to group by the stand and then look for the times (count) that the SIBT for ACx (first aircraft) is greater than AIBT and less than AOBT for ACy (other aircraft), but how to structure it is killing me.

 

factIdstandaircraftRegistrationaldtsibtaibtsobtaobtatot 
111ECJZL2015-01-01 16:22:15.0002015-01-01 16:55:00.0002015-01-01 16:45:00.0002015-01-01 17:55:00.0002015-01-01 17:50:00.0002015-01-01 17:57:53.000
27ECGRP2015-01-01 08:48:57.0002015-01-01 09:00:00.0002015-01-01 09:01:00.0002015-01-01 11:30:00.0002015-01-01 11:24:00.0002015-01-01 11:29:34.000
37ECGRP2015-01-01 13:17:54.0002015-01-01 13:30:00.0002015-01-01 13:24:00.0002015-01-01 16:00:00.0002015-01-01 15:54:00.0002015-01-01 16:00:32.000
47ECJQL2015-01-01 20:23:04.0002015-01-01 20:30:00.0002015-01-01 20:31:00.0002015-01-01 21:00:00.0002015-01-01 20:56:00.0002015-01-01 21:01:03.000
5T1EIDCO2015-01-01 17:52:53.0002015-01-01 18:25:00.0002015-01-01 18:11:00.0002015-01-01 19:00:00.0002015-01-01 19:02:00.0002015-01-01 19:11:34.000
2 REPLIES 2
Community Support Team
Community Support Team

Re: DAX - count inside group

Hi @AlanGroskreutz,

 

Can you post the expected result from your sample data please? Do you mean it like the snapshot shows?

DAX_count_inside_group

 

The same stand, the airplane "EC2" delayed the inblock of airplane "ECGRP", then count the times of such occurrences?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AlanGroskreutz Frequent Visitor
Frequent Visitor

Re: DAX - count inside group

Hi Dale, yes, that would be an example. Thanks for any help.