Forum Discussion

dphillips's avatar
dphillips
Helper IV
8 years ago
Solved

Filter data using last value in a table

I have some values and I find the average of these values over a field called YearSem     I use a line graph to visualise this. See below.   How can I pick out the last value (3.49) and put ...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    This would also work.  The issue with your query is when you bring a calculated measure through to the CALCULATE you change the filter context (which sometimes is what you want - but not in your case)

     

    Last LP Ave = 
    CALCULATE(
            AVERAGE(AveLPs[Average of AveLP]),
            FILTER(AveLPs,AveLPs[YearSem]=MAX(AveLPs[YearSem]))
            )
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

Hi dphillips

 

You have a filter context problem that can be solved by re-arranging your measure to be as follows

 

Last LP Ave = 
var d = CALCULATE(MAX(AveLPs[YearSem]))
return 
    CALCULATE(
            AVERAGE(AveLPs[Average of AveLP]),
            FILTER(AveLPs,AveLPs[YearSem]=d))
dphillips's avatar
dphillips
Helper IV
8 years ago

Brilliant - thanks for your help