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
bhanu_gautam
Super User
Super Usersophtee , Try using
DAX
Hch_Proj =
VAR Flk_1 = Table1[Flk#]
VAR temp_1 = FILTER(Table2, Table2[FlockNo] = Flk_1)
VAR sorted_temp_1 = TOPN(5, temp_1, Table2[Date], DESC) // Assuming you have a Date column to sort by
RETURN AVERAGEX(sorted_temp_1, Table2[Odata_%Hch])
sophteeHelper I
Helper Ibhanu_gautam how can I check to see what 5 values are being used?