Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Returning value using MAXX

Hi all, 
 
I've previously had assistance on this, but it doesn't seem to work with a slightly different set of data; so the calculated column is:
 
LastestContract = 
VAR ContractNo = 'Table'[Main Contract]
RETURN
MAXX(FILTER( ALL( 'Table' ) ,[Main Contract] = ContractNo ), 'Table'[Contract] )

But the new data now has blanks in it, so for example looks more like

 

 

NameContractMain Contract NoDate
Jones1001 01/01/2020
Smith1005 01/06/2020
Smith2000100501/05/2020
Stevens2500 01/01/2020
Stevens2750250001/01/2021

 

 The end result I would want would be

NameLatest ContractDate
Jones100101/01/2020
Smith200001/05/2020
Stevens275001/01/2021

 

So basically returning the highest column value related to a main contract number, and the related date (regardless of it that date is earlier or later than the initial contract).

 

I've tried to reuse the previous code, but don't seem to have any joy - am I missing something simple?

 

Many thanks!

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    Create a new column to replace the blank values:

    main = IF(ISBLANK('Table'[Main Contract No]),'Table'[Contract],'Table'[Main Contract No])

    Then use the maxx function:

    LastestContract = MAXX(FILTER( ALL( 'Table' ) , 'Table'[main]=EARLIER('Table'[main])), 'Table'[Contract] )

     

    Best Regards,

    Jay

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    Create a new column to replace the blank values:

    main = IF(ISBLANK('Table'[Main Contract No]),'Table'[Contract],'Table'[Main Contract No])

    Then use the maxx function:

    LastestContract = MAXX(FILTER( ALL( 'Table' ) , 'Table'[main]=EARLIER('Table'[main])), 'Table'[Contract] )

     

    Best Regards,

    Jay

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Ah - thanks - I was so stuck in my way of thinking that I didn't consider making another column for purposes of calculating. 

       

      Thanks again! 

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , In visual Take Name, Max of Date and this measure

     

    Contract latest =
    VAR __id = MAX ('Table'[Name] )
    VAR __date = CALCULATE ( MAX('Table'[date] ), ALLSELECTED ('Table' ), 'Table'[ID] = __id )
    return
    CALCULATE ( Max ('Table'[Contract Main] ), VALUES ('Table'[Name] ),'Table'[Name] = __id,'Table'[date] = __date )

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      amitchandak Thanks I will try this althogh note that your measure refers to an ID column in my table that doesn't exist.

       

      Just to be clear the table formatting hasn't seeemed to work for me, so the entries are

       

      Name / Contract / Main Contract No / Date

      1. Jones / 1001 / (blank) / 01/01/2020
      2. Smith / 1005 / (blank) / 01/06/2020
      3. Smith / 2000 / 1005 / 01/05/2020
      4. Stevens / 2500 / (blank) / 01/01/2020
      5. Stevens / 2750 / 2500 / 01/01/2021 

       

      With results being:

      Name / Latest Contract / Date

      1. Jones / 1001 / 01/01/2020
      2. Smith / 2000 / 01/05/2020
      3. Stevens / 2750 / 01/01/2021