Forum Discussion

marmarfe's avatar
marmarfe
New Member
8 years ago
Solved

Group by - last date

Hi, 

 

I'm quite new on the use of Power BI and I'm stuck trying to solve this probelm since almost one week. 

 

I have a table containing registers of different flights, it looks as follow:

 

FlightIDCategoryDateAircraft RegistrationAirport DEPAirport ARR
34406Production06-JanEEEHUENHUEN
34430Production07-JanEEEHUENHUEN
34433Production08-JanEEEHUENHUEN
34458Production09-JanEEEHUENHUEN
34589Production12-JanEEEHUENHUEN
34383Production05-JanDDDEBAWEBAW
34407Production06-JanDDDEBAWEBAW
34434Ferry08-JanDDDEBAWEHTE
34515Ferry11-JanDDDEBAWEGTC
34579Ferry11-JanDDDEGTCEBAW
34591Test flight EQT12-JanDDDEBAWEBAW
34368Ferry05-JanCCCEBAWEHTE
34232Production02-JanBBBHUENHUEN
34320Production03-JanBBBHUENHUEN
34346Production04-JanBBBHUENHUEN
34231Ferry01-JanAAAEDDGEBAW
34390Production05-JanAAAEBAWEDDG
34394Production06-JanAAAEDDGEDDG
34427Production06-JanAAAEDDBEBAW

 

What I need is to know where each plane has landed last time, the result should be a table with 3 columns as it is shown on the sample below:

 

DateAircraft RegistrationAirport ARR
06-JanAAAEBAW
04-JanBBBHUEN
05-JanCCCEHTE
12-JanDDDEBAW
12-JanEEEHUEN

 

I have tried to use the function Group By (registration) selection the MAX value of the date, but I cannot add the column airport because I have to include an "expresion", getting the wrong result. I have  also tried with the function summarize, but I haven't succed.

 

Any idea about how to solve this problem?

 

Thanks a lot!

 

Marcos

  • Vvelarde's avatar
    Vvelarde
    8 years ago

    marmarfe

     

    Hi, in Query Editor, you can do

     

    1. Duplicate the Query

     

    2. Group By:

     

     

    3. Merge Both Tables

     

     

    4. Expand it select the columns that you desire

     

     

    5. Ready

     

     

    Regards

     

    Victor

    Lima - Peru

5 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    8 years ago

    marmarfe

     

    Hi, in Query Editor, you can do

     

    1. Duplicate the Query

     

    2. Group By:

     

     

    3. Merge Both Tables

     

     

    4. Expand it select the columns that you desire

     

     

    5. Ready

     

     

    Regards

     

    Victor

    Lima - Peru

    • marmarfe's avatar
      marmarfe
      New Member
      8 years ago

      Thanks Victor, It works now!!!

       

      I was focus on the columns that I wanted to have on my output table, and I never consider to link them by the ID number.

       

      Regards

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        marmarfe,

        Glad to hear the issue is solved, you can accept appropriate reply as answer to close this thread.

        Regards,
        Lydia

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank You! Group By is an excellent solution. Much respect to your name Vverlarde. Mucas Gracias.

  • parry2k's avatar
    parry2k
    Super User
    8 years ago

    Add two measures, one for latest date and other for latest arrival airport

     

    Latest Date = CALCULATE(MAX(Table1[Date]), Filter(ALLSELECTED(Table1[Aircraft Registration]), Table1[Aircraft Registration] = MAX(Table1[Aircraft Registration])))

Last Arrival Airport = Calculate(FIRSTNONBLANK(Table1[Airport ARR],1), Filter(Table1, Table1[Latest Date] = Table1[Date]))