Create a column by finding the max value of a group and returning another value

[PowerBI_Newb]

Please help! My brains hurts 🙄

I'd like to find the most recent COMPANY assigned to each FILE listed below.

The most recent COMPANY (LATEST_COMPANY) should be calculated by:

1. Finding the most recent FILE_PERIOD_NUMBER for each FILE group and
2. Reporting back the COMPANY of the most recent FILE_PERIOD_NUMBER in the LATEST_COMPANY column

YEAR	FILE	FILE_PERIOD_NUMBER	COMPANY	LATEST_COMPANY
2001	123	123-01	Company1	Company2
2002	123	123-02	Company1	Company2
2003	123	123-03	Company2	Company2
2002	234	234-02	Company5	Company3
2003	234	234-03	Company4	Company3
2004	234	234-04	Company3	Company3

How would I do this by either creating a column in Power Query or DAX?

So far, all I've been able to do is work out the most recent FILE_PERIOD_NUMBER using the following:

Max_File_Period_Number = CALCULATE(max(Files[FILE_PERIOD_NUMBER]),ALLEXCEPT(Files,Files[FILE]))

Many thanks in advance

[v-luwang-msft · Accepted solution]

Hi    @PowerBI_Newb 

You can use the following dax to create a measure:

last company2 =
VAR max1 =
    CALCULATE (
        MAX ( 'Table1'[year] ),
        FILTER ( ALL ( 'Table1' ), 'Table1'[FILE] = MAX ( 'Table1'[FILE] ) )
    )
VAR last =
    CALCULATE (
        MAX ( 'Table1'[COMPANY] ),
        FILTER (
            ALL ( 'Table1' ),
            'Table1'[YEAR] = max1
                && 'Table1'[FILE] = MAX ( 'Table1'[FILE] )
        )
    )
RETURN
    last

then create a new column base on the measure:

last company = [last company2]

Final you will see like below:

click here to download if you need!

Wish  it is helpful for you!

Best Regard

Lucien Wang

[PowerBI_Newb]

That's AWESOME - taught me so much...thank you!
Would you know how to do this as a measure? - it's taking a really long time to run

[Vera_33]

Hi @PowerBI_Newb 

I am using the [Custom] column as the original column is not number, and your max

Last_Company =
VAR CurFile =
    SELECTEDVALUE ( Table1[FILE] )
VAR maxFilePeriod =
    CALCULATE ( MAX ( Table1[Custom] ), ALLEXCEPT ( Table1, Table1[FILE] ) )
RETURN
    MAXX (
        FILTER ( ALL ( Table1 ), [Custom] = maxFilePeriod && Table1[FILE] = CurFile ),
        Table1[COMPANY]
    )

[PowerBI_Newb]

Vera thanks so much for replying back 🙂

I'm having trouble getting this working - I end up with blank values.

Can I confirm, what value is in the [custom] column?

I thought you meant to change it to [FILE_PERIOD_NUMBER], but as I've said this just produces a blank result.

Thanks again for your help 

[Vera_33]

Hi @PowerBI_Newb 

It is the last 2 digits of your original column, I had this column in the M solution. You can also wrap RIGHT([your original column],2) to replace my [custom] column. Have a try:)

[PowerBI_Newb]

Hi @Vera_33 - I still get a blank column...I must be doing something wrong here.

Here's what I've done:

https://filebin.net/g0dm5sp2qrssijys/Testing.pbix?t=449jz9vp

Thanks again for your help

[Vera_33]

Hi @PowerBI_Newb 

The one you wrote is a calculated column, not a measure...if you need a column

Column = 
VAR CurFile = Table1[FILE] 
VAR maxFilePeriod =
    CALCULATE ( MAX ( Table1[Custom] ), ALLEXCEPT ( Table1, Table1[FILE] ) )
RETURN
    MAXX (
        FILTER ( ALL ( Table1 ), [Custom] = maxFilePeriod && Table1[FILE] = CurFile ),
        Table1[COMPANY]
    )

[Vera_33]

Hi @PowerBI_Newb 

This is one way to do in Power Query, adding a column as a result, paste it in Advanced Editor

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMjIwMFTSUTI0MoaQumCuc35uQWJepaFSrA5YiRGqEiMsSoxRlRgjlBghm2JkbAIhUUwxRTYFSQmSKSYwJSaoSkwQSoyVYmMB",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [YEAR = _t, FILE = _t, FILE_PERIOD_NUMBER = _t, COMPANY = _t]
  ),
  #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.End([FILE_PERIOD_NUMBER], 2)),
  #"Changed Type" = Table.TransformColumnTypes(#"Added Custom", {{"Custom", Int64.Type}}),
  #"Grouped Rows" = Table.Group(
    #"Changed Type",
    {"FILE"},
    {
      {"max", each List.Max([Custom]), type nullable number},
      {
        "allrows",
        each _,
        type table [
          YEAR = nullable text,
          FILE = nullable text,
          FILE_PERIOD_NUMBER = nullable text,
          COMPANY = nullable text,
          Custom = nullable number
        ]
      }
    }
  ),
  #"Merged Queries" = Table.NestedJoin(
    #"Grouped Rows",
    {"FILE", "max"},
    #"Changed Type",
    {"FILE", "Custom"},
    "Grouped Rows",
    JoinKind.LeftOuter
  ),
  #"Expanded Grouped Rows" = Table.ExpandTableColumn(
    #"Merged Queries",
    "Grouped Rows",
    {"COMPANY"},
    {"Last_COMPANY"}
  ),
  #"Removed Other Columns" = Table.SelectColumns(
    #"Expanded Grouped Rows",
    {"allrows", "Last_COMPANY"}
  ),
  #"Expanded allrows" = Table.ExpandTableColumn(
    #"Removed Other Columns",
    "allrows",
    {"YEAR", "FILE", "FILE_PERIOD_NUMBER", "COMPANY"},
    {"YEAR", "FILE", "FILE_PERIOD_NUMBER", "COMPANY"}
  )
in
  #"Expanded allrows"

[PowerBI_Newb]

@Vera_33 That's AWESOME - taught me so much...thank you!
Would you know how to do this as a measure? - it's taking a really long time to run