locked
Problem with dates in DAX RRS feed

  • Question

  • Hello everyone, I´m trying to make this function work but it just doesnt want to, I have this dataset of dates and there is one ID for many of them and what I want to do is to chose the lowest and highest date of all the dates inside one ID for that I used the next DAX formula.

     

    MAX = CALCULATE(MAX('2017 - 2018'[SALIDA FIX]),FILTER('2017 - 2018','2017 - 2018'[FOLIO]))

     

    AND

     

    MAX = CALCULATE(MIN('2017 - 2018'[REGRESO FIX]),FILTER('2017 - 2018','2017 - 2018'[FOLIO]))

     

    REGRESO FIX = RETURN 

    SALIDA FIX = DEPARTURE

    FOLIO = ID

     

    But when I put the mesure in a table it just shows me the min/max date of the row not the max/min of the whole dates inside of the ID, I want the min/max of all the dates inside one ID in all rows of a new column

    Thanks a lot for helping, I really appreciate

    Wednesday, February 13, 2019 5:18 PM

Answers

  • Try using ALLEXCEPT instead of FILTER. When you call CALCULATE in a calculated column the row context is transitioned to a filter context. What this effectively does is to calculated across every row that has the same set of values as the current row, but you can override this by using either ALL or ALLEXCEPT

    eg.

    MAX = CALCULATE(MAX('2017 - 2018'[SALIDA FIX]),ALLEXCEPT('2017 - 2018','2017 - 2018'[FOLIO]))

    MIN = CALCULATE(MIN('2017 - 2018'[REGRESSO FIX]),ALLEXCEPT('2017 - 2018','2017 - 2018'[FOLIO]))


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by MaTiKuTo Thursday, February 14, 2019 8:16 PM
    Wednesday, February 13, 2019 9:39 PM

All replies

  • Try using ALLEXCEPT instead of FILTER. When you call CALCULATE in a calculated column the row context is transitioned to a filter context. What this effectively does is to calculated across every row that has the same set of values as the current row, but you can override this by using either ALL or ALLEXCEPT

    eg.

    MAX = CALCULATE(MAX('2017 - 2018'[SALIDA FIX]),ALLEXCEPT('2017 - 2018','2017 - 2018'[FOLIO]))

    MIN = CALCULATE(MIN('2017 - 2018'[REGRESSO FIX]),ALLEXCEPT('2017 - 2018','2017 - 2018'[FOLIO]))


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by MaTiKuTo Thursday, February 14, 2019 8:16 PM
    Wednesday, February 13, 2019 9:39 PM
  • It worked! thanks a lot regards from México
    Thursday, February 14, 2019 3:41 PM