Forum Discussion
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?
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
3 Replies
- Hi - Use the LastNonBlank Function..
D 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
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
