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

Get last value in given row

I'm trying to grab the last non-blank value in a given row with unknown number of values. So for some rows it could be column 5 but others it would be column 8.

 

In Excel, I can use =LOOKUP(2,1/(A:A<>""),A:A) to find this.

What would be the equivalent in PowerBI?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: Get last value in given row

Hi @sam850701,

Based on my test, you could refer to below steps:

1.Sample data

1.PNG

2.Add an index column in query editor.

2.PNG

3.Create a measure and add to a card visual. Now you could see the result.

Measure 3 = Var index= CALCULATE(MIN('Table1'[Index]),FILTER('Table1','Table1'[Value]=BLANK()))
return CALCULATE(MAX('Table1'[Value]),FILTER('Table1',Table1[Index]=index-1))

3.PNG

 

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/aw96lwmbf1sqice/Get%20last%20value%20in%20given%20row.pbix?dl=0

 

Regards,

Daniel He

 

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
davehus Member
Member

Re: Get last value in given row

Hi - Use the LastNonBlank Function..

D
Highlighted
Community Support Team
Community Support Team

Re: Get last value in given row

Hi @sam850701,

Based on my test, you could refer to below steps:

1.Sample data

1.PNG

2.Add an index column in query editor.

2.PNG

3.Create a measure and add to a card visual. Now you could see the result.

Measure 3 = Var index= CALCULATE(MIN('Table1'[Index]),FILTER('Table1','Table1'[Value]=BLANK()))
return CALCULATE(MAX('Table1'[Value]),FILTER('Table1',Table1[Index]=index-1))

3.PNG

 

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/aw96lwmbf1sqice/Get%20last%20value%20in%20given%20row.pbix?dl=0

 

Regards,

Daniel He

 

 

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

Re: Get last value in given row

Hi @sam850701,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

 

Regards,

Daniel He

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