Forum Discussion

NMOORE's avatar
NMOORE
Helper II
2 years ago
Solved

Dax Table Summarize using Lookup

Hi,

 

I want to summarize a table keeping the corresponding values/Dates from a neighbouring column after an expression.

 

As the example shows I'm hoping to summarize the animals, calculate the earlisest (min) Analysis Date, and keep it's neighbouring FInalise Date.

 

 

Appreciate any help.

 

Thanks

  • sevenhills's avatar
    sevenhills
    2 years ago
    IDEAL_Backtest_Details = 
ADDCOLUMNS( 
    SUMMARIZE(Backtest_Details, 
              Backtest_Details[Animals], 
              "Analysis Date",  min(Backtest_Details[Analysis Date])
    )

    , "Final Date", 
            LOOKUPVALUE(Backtest_Details[Final Date], 
                  Backtest_Details[Animals], [Animals],
                  Backtest_Details[Analysis Date],  [Analysis Date])
 )

     

    something like this?

     

    IP: Backtest_Details

     

    OP:

     

     

  • tamerj1's avatar
    tamerj1
    2 years ago

    Hi NMOORE 

    if you're looking for a calculated table solution, you may try

    NewTable =
    GENERATE (
    VALUES ( 'Table'[Animals] ),
    CALCULATETABLE (
    TOPN (
    1,
    SELECTCOLUMNS (
    'Table',
    "Analysis Date", 'Table'[Analysis Date],
    "Finalise Date", 'Table'[Finalise Date]
    ),
    'Table'[Analysis Date], ASC
    )
    )
    )

  • NMOORE's avatar
    NMOORE
    2 years ago

    Thanks alot for this, I was playing around with it for a while trying to do exactly this but just couldnt get there. Much appreciated.

7 Replies

  • sevenhills's avatar
    sevenhills
    Super User
    2 years ago
    IDEAL_Backtest_Details = 
ADDCOLUMNS( 
    SUMMARIZE(Backtest_Details, 
              Backtest_Details[Animals], 
              "Analysis Date",  min(Backtest_Details[Analysis Date])
    )

    , "Final Date", 
            LOOKUPVALUE(Backtest_Details[Final Date], 
                  Backtest_Details[Animals], [Animals],
                  Backtest_Details[Analysis Date],  [Analysis Date])
 )

     

    something like this?

     

    IP: Backtest_Details

     

    OP:

     

     

    • NMOORE's avatar
      NMOORE
      Helper II
      2 years ago

      Thanks alot for this, I was playing around with it for a while trying to do exactly this but just couldnt get there. Much appreciated.

      • sevenhills's avatar
        sevenhills
        Super User
        2 years ago

        Please mark my reply as solution if you like it and also helps for the folks in future those who refer this thread.

  • tamerj1's avatar
    tamerj1
    Community Champion
    2 years ago

    Hi NMOORE 

    if you're looking for a calculated table solution, you may try

    NewTable =
    GENERATE (
    VALUES ( 'Table'[Animals] ),
    CALCULATETABLE (
    TOPN (
    1,
    SELECTCOLUMNS (
    'Table',
    "Analysis Date", 'Table'[Analysis Date],
    "Finalise Date", 'Table'[Finalise Date]
    ),
    'Table'[Analysis Date], ASC
    )
    )
    )

    • NMOORE's avatar
      NMOORE
      Helper II
      2 years ago

      Hi, Thanks so much for your reply, I was trying to understand summarize and add columns better as I was half-way there but will look at understanding what you did here next week. Thanks again

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    No DAX needs to be harmed for this,  You can do this entirely in Power Query.  Sort by Analysis Date ascending and then deduplicate by Animals.

    • NMOORE's avatar
      NMOORE
      Helper II
      2 years ago

      Yes, easy in Power Query, already accomplished. The question was for DAX