Get last value in given row

[Anonymous]

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?

[v-danhe-msft]

Hi @Anonymous,

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

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

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

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

[davehus]

Hi - Use the LastNonBlank Function..

D