cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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])) 

FIRSTNONBLANK: Returns the first value in the column, column, filtered by the current context, where the expression is not blank.

ALLEXCEPT: Removes all context filters in the table except filters that have been applied to the specified columns.

 

Let's start with FIRSTNONBLANK. When it says "Returns the first value in the column", and let's say column is a Date/Time column not sorted in any way, does this function somehow perform a sorting to determine what the "FIRST" value should be? In my use-case, I am using this function to get the first date it was seen based on an email address.

 

What does the 1 actually mean in the FIRSTNONBLANK function above? Is that a boolean or literally the number 1? And why is it needed / what does it do when it's the expression? Does it just return a 1?

 

Now ALLEXCEPT. Is see that this is being used as a filter for CALCULATE, but I'm not sure what it's actually doing. "Removes all context filters" doesn't mean anything to me yet as a new user.

 

Ultimately, I don't understand how these three functions return the first seen date. Thanks in advance.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

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.

 

 

 

 

 

 

 

 

 

 

 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Super User IV
Super User IV

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.

 

 

 

 

 

 

 

 

 

 

 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Super User I
Super User I

HI @jaradc

 

This is not mentioned in the documentation of the function but you are right

 

"FirstNonBlank /LastNonBlank return the first/last value respectively in the column…..after sorting the column in its native Ascending Order….column, filtered by the current context, where the expression is not blank."

 

You can check this post as well

 

http://www.excelnaccess.com/using-firstnonblank-lastnonblank-in-dax/

Regards,
Zubair


Microsoft
Microsoft

HI @jaradc

 

The FIRSTNONBLANK function is just like using a MIN function.  It will just return the oldest date with respect to other filter functions


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@jaradc

 

As phil mentioned ...this function is very much like the Min function.

 

The second argument of this function can be anything that returns a nonblank value for the column.

You can use 0,1,"YourName" or a DAX measure or any other expression that returns a single value

 

But here is one important difference...

 

Lets take this table

 

Product Amount
A  
B  
C 145
D 164
E 179
F 164

 

Check the results of following MEASUREs in the TABLE

 

 

Measure 1 = FIRSTNONBLANK(Table1[Product],1)
Measure 2= FIRSTNONBLANK(Table1[Product],CALCULATE(sum(Table1[Amount])))
Measure 3 = min(Table1[Product])

FNbklank.png

 

 

 

The second argument of FirstNonBlank is thus the differentiator

Regards,
Zubair


Community Champion
Community Champion

@jaradc

This post by @MattAllington should help answer your questions 

https://exceleratorbi.com.au/lastnonblank-explained/

There are also additional links at the bottom of the article

Matt’s site has a ton of helpful information

Good Luck! Smiley Happy

 

Frequent Visitor

This quote from ExceleratorBI was really helpful to understand why placing 1 in as the second parameter works:

LASTNONBLANK iterates through the Table[Column] and then checks to see if the second parameter has a value.  The number 1 always has a value of course, so placing 1 as the second parameter has the same effect as just ignoring this parameter.

 

That link was really helpful. Thanks

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors