LASTNONBLANK function DAX

LASTNONBLANK function is a Power BI time intelligence function in DAX which returns the last value in the column filtered by the current context for which the expression has a non blank value.

It returns a table containing a single column and single row with the computed last value.

SYNTAX

LASTNONBLANK(<column>,<expression>)

Following are some considerations which are recommended by Microsoft on using LASTNONBLAK function:

• The column argument can be any of the following:
  1. A reference to any column.
  2. A table with a single column.
  3. A Boolean expression that defines a single-column table
• This function is typically used to return the last value of a column for which the expression is not blank.
• This function is not supported for use in Direct Query mode when used in calculated columns or row-level security (RLS) rules.

Lets look at an example of using LASTNONBLANK in Power BI.

Here we have a sample data named Officesupply.

Copy sample data:

Lets create a simple report which display the total quantity by item.

Assume that, you wan to see the last ItemId for which Quantity value is non blank, lets use the  LASTNONBLAK DAX function.

LNB =
LASTNONBLANK (
    OfficeSupply[ItemId],
    CALCULATE ( SUM ( OfficeSupply[Quantity] ) )
)

Once you create the measure LNB, lets take a card visual to see the output of measure.

You can see, it returns the last ItemId that is 11 for which quantity column has non blank value.

LASTNONBLANK function returns a last value for which an expression has non blank value, if you take a look at the last ItemId in report that is 12 but it has a blank quantity value, so the LASTNONBLAK function returns the last ItemId value as 11 because for this ItemId the quantity column has non blank quantity value.

Lets take one more example and this time lets see the Last Item for which the quantity has non blank value.

LSB_Item =
LASTNONBLANK (
OfficeSupply[Item],
CALCULATE ( SUM ( OfficeSupply[Quantity] ) )
)

Once you create the measure LSB_Item, lets take a card visual to see the output of measure.

Also note that, As this time you want to see the last item for which quantity has non blank value, so make sure you have all item sorted in ascending order first so that you can easily verified the output returned by measure LSB_Item is correct or not.

So just click on More option (ellipsis …) at the top of visual, then select Sort ascending, also Sort by Item.

Once you do this, you will see this time data in visual is sorted by Item in ascending order as shown below. Now it will be easy for you to verify the result quickly.

Lets drag the measure LSB_Item into card visual to see the last item that has non blank quantity.

You can see the last item that has non blank quantity is Water Bottle.

Also Read..

SAMEPERIODLASTYEAR

TOTALYTD

 5,122 total views,  1 views today