Forum Discussion

jaradc's avatar
jaradc
Frequent Visitor
8 years ago
Solved

How does FIRSTNONBLANK and ALLEXCEPT together with CALCULATE return the first date for a given ID?

I'm trying to understand HOW the following calculation is returning the first date:     FirstDate = CALCULATE(FIRSTNONBLANK(mytable[activitydate], 1), ALLEXCEPT(mytable, mytable[emailaddress]))  ...
  • Greg_Deckler's avatar
    8 years ago

    On FIRSTNONBLANK, it's not documented the best. Yes, it is a time intelligence function so it is intelligent about dates and times so it will find the "earliest" date in a series of unsorted dates.

     

    So, I'm going to give you my perspective on this even though working with FIRSTNONBLANK can sometimes be tricky:

     

    Suppose you have a 2 tables like this:

     

    Dates

    Date

    Monday, January 1, 2018
    Sunday, January 1, 2017
    Friday, January 1, 2016
    Wednesday, December 12, 2018
    Tuesday, December 12, 2017
    Monday, December 12, 2016

     

    DateValues

    Date                                                  Value

    Monday, January 1, 2018 10
    Sunday, January 1, 2017 14
    Friday, January 1, 2016  
    Wednesday, December 12, 2018 22
    Tuesday, December 12, 2017 55
    Monday, December 12, 2016 60

     

     

    And there is the obvious relationship. 

     

    Now you have a measure:

     

    Measure 1 = FIRSTNONBLANK(Dates[Date],1) 

    This is going to return 1/1/2016. The expression isn't doing anything in this case. 1 is always 1 and is never blank, so all dates apply and you get the first.

     

    But, if you do something like this:

     

    Measure 2 = FIRSTNONBLANK(Dates[Date],CALCULATE(SUM(DateValues[Value]),RELATEDTABLE(DateValues)))

    You get back 12/12/2016 because that is the earliest date where the expression is essentially not null or blank.

     

    Now, ALLEXCEPT removes all other filter contexts except for the filters on the columns specified. This means that if you have other slicers on other columns, ALLEXCEPT removes those filters but keeps the filter that you have on, in your case emailaddress. So, if you had a filter on "First Name" and on "Last Name" for example, the calculation of FirstDate would not be affected by those filters as it would normally, it would only care about the filter on emailaddress.

     

    CALCULATE is just a way to apply a specific filter context to something that you are evaluating.