Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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
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
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
User | Count |
---|---|
99 | |
86 | |
78 | |
75 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |