Measure expression to include table look up

[richard_gale]

Hi - looking for some help, please, with an expression for a Measure (actually a Reference line expression).

This following expression works -

Sum({<File_Date={'31/12/2022'}>}[FTE Actual])

..but I want the expression to look up a table rather than include the text '31/12/2022'

Independently, this expression works (as a Dimension expression) to look up the value I want in the table

=({<MonthEnd={'Yes'}>}[Latest_ME])

but I have been unable to substitute the above 'look up' expression in the Measure expression for the text inside the ' '

Google hasn't been of any help at all unless I'm just searching using incorrect terminology.

Thanks, Richard

[MayilVahanan · Accepted solution]

Hi

Are you looking for dynamic date and merge the expressions? if so, try like below

Sum({<File_Date={"$(=Date(Max(File_Date), 'DD/MM/YYYY'))"}, MonthEnd={'Yes'}>}[FTE Actual])

[richard_gale]

Hi MayilVahanan,

Thank you - it works!

But I didn't really understand why since there was no reference to the Latest_ME field in the table from which to take the value (31/12/2022) - just the field from which to perform the look up (MonthEnd). I note that if I remove the MonthEnd part of the expression to leave the following, it still works which suggests to me the table isn't being used at all - rather the expression just looks for the maximum (or latest) File_Date in the table with FTE Actual in it - which basically does the same thing.

=Sum({<File_Date={"$(=Date(Max(File_Date), 'DD/MM/YYYY'))"}>}[FTE Actual])

So thanks very much again for solving my issue, but I'd still like to know if there is a way I could have used the table for a look up to derive the Latest_ME date?

Cheers, Richard

[MayilVahanan]

Hi

You can store into variable like vMaxLatest =Max({<MonthEnd={'Yes'}>}[Latest_ME]) 

and assign in the expression

=Sum({<File_Date={"$(=Date($(vMaxLatest ), 'DD/MM/YYYY'))"}>}[FTE Actual])

[richard_gale]

Thanks again, MayilVahanan - I'm interested in the use of a variable since I'm still learning and haven't used these before, but I was unable to get your expression to work. I may be doing something wrong with the variable definition but it looks like it returns the correct value?

Thanks again for trying to help me learn

Cheers, Richard