Forum Discussion
sophtee
Helper I
Helper IGet the last 5 value from a look up
Hello everyone, Currently I am using 2 tables in Power BI where I take the Flk# from Table 1 look-up the Table 2 Flk# and return the average of all the Hch value. However I would just like to find t...
Hi sophtee,
According to your description, you can use the TOPN function to get the first five data under the same number to do the calculationHch Proj = VAR Flk_1 = 'Table 1'[Flk#] VAR temp_1 = FILTER('Table 2', 'Table 2'[FlockNo] = Flk_1) VAR last_5 = TOPN(5, temp_1, 'Table 2'[EndHchDate], DESC) RETURN AVERAGEX(last_5, 'Table 2'[Odata_%Hch])TOPN function (DAX) - DAX | Microsoft Learn
Because the data you provided is incomplete, the end result may be different from what you wantBest regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
danextian
Super User
Super UserHi sophtee
How do you determine which rows are the last ones? Also, please provide a workable sample data (not an image) and your expected result from that.
sophteeHelper I
Helper ITable 1
Table 2 : when a flock is select from table 1 it looks up table 2 and filters the top 5 hch data and give the average