cancel
Showing results for
Did you mean:
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
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

2.Add an index column in query editor.

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

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
Member

Re: Get last value in given row

Hi - Use the LastNonBlank Function..

D
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

2.Add an index column in query editor.

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

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.
Highlighted
Community Support Team

Hi @sam850701,