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'[...
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

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