Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
webportal
Impactful Individual
Impactful Individual

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
v-yiruan-msft
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

4 REPLIES 4
PierreArchen
Regular Visitor

Can you tell me more why this formula work ? Why use min function ? 

v-yiruan-msft
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.

@v-yiruan-msftThank you!

mahoneypat
Employee
Employee

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.