cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
webportal
Post Prodigy
Post Prodigy

Get the last non blank value of a column with DAX

webportal_0-1614548109052.png

I want a Column to find the last non blank value from "Class" following the order of the "Date" column.

Thus, the first 3 rows will be empty.

Rows from 31/01/2016 to 30/04/2016 will be A.

 

And ALL the remaing rows will be B.

 

I know the following will work:

Coluna =
VAR EstaData = Tabela[Data]
VAR DataMax = CALCULATE(MAX(Tabela[Data]), FILTER(ALL(Tabela), Tabela[Data]<= EstaData && LEN(Tabela[Class])>0))
RETURN
CALCULATE(MAX(Tabela[Class]), FILTER(ALL(Tabela), Tabela[Data] = DataMax))
 
But... isn't there a better/cleaner way??

 

Thanks for helping!

1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

Hi @webportal ,

You can create a calculated column as below:

Coluna =
CALCULATE (
    LASTNONBLANK ( 'Tabela'[Class], MAX ( 'Tabela'[Data] ) ),
    ALL ( 'Tabela' )
)

Get the last non blank value of a column with DAX.JPG

Best Regards

 

 

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
yingyinr
Community Support
Community Support

Hi @webportal ,

You can create a calculated column as below:

Coluna =
CALCULATE (
    LASTNONBLANK ( 'Tabela'[Class], MAX ( 'Tabela'[Data] ) ),
    ALL ( 'Tabela' )
)

Get the last non blank value of a column with DAX.JPG

Best Regards

 

 

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

@yingyinrThank you!

mahoneypat
Super User IV
Super User IV

Please try this measure expression, replacing T1 with your actual table name.

LNBV = LASTNONBLANKVALUE(T1[Date], MIN(T1[Class]))
Pat




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors