locked
Dynamic Filter ( FILTER( tablex, values in columnx = value in columnx and currrent row ) RRS feed

  • Question

  • Hello,

    I would like to build a column that filter values depending on the value on the current row.

    Let me give you a simple example of what I would like to do.

    I have a table customerorders with 2 fields: customernumber and dateorder (which is a date).

    I would like to now the last dateorder for every customernumber.

    I thought about something like that:

    =CALCULATE(LASTDATE(customerorders[dateorder]),FILTER(customerorders,customerorders[customernumber]=customerorders[customernumber]))

    But I obviously lack some knowledge about how to deal with the 2nd argument of my filter.

    Thanks

    Tuesday, January 7, 2014 4:09 PM

Answers

  • try this one:

    =CALCULATE(MAX(customerorders[dateorder]), FILTER(ALL('customerorders'),customerorders[customernumber] = EARLIER(customerorders[customernumber])))

    you need to use EARLIER to refer to customernumber of the current row

    hth,
    gerhard


    - www.pmOne.com -

    • Marked as answer by JG Meillaud Tuesday, January 7, 2014 4:46 PM
    Tuesday, January 7, 2014 4:42 PM
    Answerer

All replies

  • try this one:

    =CALCULATE(MAX(customerorders[dateorder]), FILTER(ALL('customerorders'),customerorders[customernumber] = EARLIER(customerorders[customernumber])))

    you need to use EARLIER to refer to customernumber of the current row

    hth,
    gerhard


    - www.pmOne.com -

    • Marked as answer by JG Meillaud Tuesday, January 7, 2014 4:46 PM
    Tuesday, January 7, 2014 4:42 PM
    Answerer
  • Hello Gerhard,

    Thanks a LOT!

    It works!

    (it also works without the ALL('customerorders') but I assume it makes the formula more "contextproof".)

    Regards,

    JG

    Tuesday, January 7, 2014 4:49 PM