Group table by key and extract one row per group

[Molotch]

The unability to inner join unrelated and calculated tables makes it very hard or maybe impossible to do certain table calculations.

I have the need to group a table per a key column and then extract one entire row per grouping by the max value in one column. I've tried everything I can think of but haven't found a way to do it.

In theory you think you should be able to scope the value column to the Key and MIN(Date) through an outer ADDCOLUMNS with a calculate filter context switch. No such luck, this brings in all Value rows from the Key grouping, the MIN(Date) column isn't used in the scoping (probably because it's an expression) so the selected value calculation in the outer scope only works if distinct(Values) returns one value per Key grouping. As soon as a key grouping has several values it returns BLANK.

ADDCOLUMNS(
            SUMMARIZECOLUMNS(
                Table[Key];
                "Date"; MIN(Table[Date])
            );
           "Value"; CALCULATE(SELECTEDVALUE(Table[Value]))
)

Any ideas on how to accomplish the above? My aim is to filter one row according to some value per grouping. Getting the max value for one column works, but I would like to use that row reference in some way to bring in the other columns from the same row.

[Zubair_Muhammad · Accepted solution]

Hi @Molotch

Try this one

=
ADDCOLUMNS (
    SUMMARIZECOLUMNS ( 'Table'[Key], "Date", MIN ( 'Table'[Date] ) ),
    "Value", CALCULATE (
        SELECTEDVALUE ( 'Table'[Value] ),
        'Table'[Date] = EARLIER ( [Date] )
    )
)

[Anonymous]

Hi Zubair,

I'm trying to apply your solution but I get the following error message regarding the Value column: "The Column "Value" cannot be found or may not be used in this expression".

When you write in your solution : 

SELECTEDVALUE ( 'Table'[Value] )

I undersdant that the word 'Table' references the original table which has the [Key] and [Date] columns in the original example, right?

Because I'm replacing 'Table' with the name of my table and I get the above error message.

[Molotch]

@Zubair_Muhammad Magic, an EARLIER in the calculate made the trick. Why didn't I think of that?!? 🙂 Thanks alot.

@nickchobotar Thanks for the M calculation. Might need it in the future.

[nickchobotar]

@Molotch

Here how this is done in M

This is the script to pull the max value

= Table.AddColumn(
   #"Grouped Rows", 
     "Custom", 
         each List.Max(Table.Column([Group], "Values")
  ))

Nick -