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

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

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

 

YEARFILEFILE_PERIOD_NUMBERCOMPANYLATEST_COMPANY
2001123123-01Company1Company2
2002123123-02Company1Company2
2003123123-03Company2Company2
2002234234-02Company5Company3
2003234234-03Company4Company3
2004234234-04Company3Company3

 

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

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

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:

11.png

 

click here to download if you need!

Wish  it is helpful for you!

 

Best Regard

Lucien Wang

 

 

View solution in original post

9 REPLIES 9
v-luwang-msft
Community Support
Community Support

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:

11.png

 

click here to download if you need!

Wish  it is helpful for you!

 

Best Regard

Lucien Wang

 

 

View solution in original post

PowerBI_Newb
Frequent Visitor

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

Hi @PowerBI_Newb 

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

 

Vera_33_0-1614750274544.png

 

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]
    )

 

 

 

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 

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:)

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

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
Solution Sage
Solution Sage

Hi @PowerBI_Newb 

 

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

 

Vera_33_0-1614742447656.png

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"

 

@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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.