locked
DAX Max value of multiple columns for every row RRS feed

  • Question

  • Hello

    I am trying to find the max date per row for a series of columns.

    In excel it is as simle as MAX(A2:D2).

    The difficulty is that any of the columns could have the max date, and nested IF statements becomes too messy.

    I was reading other questions and saw the answer "Do it instead in the linked table." by David Hager to a similar question, but I am not sure what this means.

    Example:

    Date A      Date B     Date C     Date D     Max Date

    5/12/12   5/11/12   5/10/12   5/11/12

    1/1/12    1/2/12      1/3/12    1/3/12

    Thanks in advance!!

    -Esme

    Thursday, October 25, 2012 2:56 PM

Answers

  • What is your data source? Is it possible to add the new column in the source?

    How many columns do you want co compare? 4 columns with nested IF:s is possible (but not a very nice solution):

    =IF('Table'[A]>='Table'[B] && 'Table'[A]>= 'Table'[C] && 'Table'[A]>= 'Table'[D],'Table'[A],IF('Table'[B]>='Table'[C]&&'Table'[B]>='Table'[D],'Table'[B],IF('Table'[C]>='Table'[D],'Table'[C],'Table'[D])))

    Best regards

    Ulf

    Friday, October 26, 2012 3:17 PM

All replies

  • Can you paste some sample data (it would help you get a faster reply if you are pasting some part fo the actual data, with the names modified if required)? As in, I am pretty sure that there are some fields on the row, and you need to find the max date for that amongst the columns...

    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    Thursday, October 25, 2012 3:13 PM
    Answerer
  • What is your data source? Is it possible to add the new column in the source?

    How many columns do you want co compare? 4 columns with nested IF:s is possible (but not a very nice solution):

    =IF('Table'[A]>='Table'[B] && 'Table'[A]>= 'Table'[C] && 'Table'[A]>= 'Table'[D],'Table'[A],IF('Table'[B]>='Table'[C]&&'Table'[B]>='Table'[D],'Table'[B],IF('Table'[C]>='Table'[D],'Table'[C],'Table'[D])))

    Best regards

    Ulf

    Friday, October 26, 2012 3:17 PM
  • It is possible to add a new column to the source and I am trying to compare 6 columns worth of dates.

    Sample data as requested by Jason Tom Thomas:

    A Start Date    A End Date B Start Date B End Date C Start Date C End Date Max Date
    4/3/2012 4/3/2012 4/4/2012 4/4/2012 4/1/2012 4/2/2012
    5/3/2012 5/2/2012 5/2/2012 5/2/2012 5/1/2012 5/2/2012
    6/5/2012 6/6/2012 5/31/2012 6/1/2012 6/2/2012 6/3/2012

    Thanks,

    Esme

    Monday, October 29, 2012 3:02 PM
  • I would say that the solution given by UlfenB is the way forward then...

    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    Monday, October 29, 2012 3:20 PM
    Answerer