LOOKUPVALUE pulling through blanks

[Multiverse_76]

Hi Datanauts

I'm struggling with a LOOKUPVALUE which is returning some blank values.  On research, I see that all of the criteria have to be met but I have double checked and the data is consistent.  I also read that I might need to use VALUES to force the whole table to be evaluated.  Any help to pull the missing values through would be greatly appreciated.

Forward Fees =

VAR EmployeeRows =

FILTER( 'Forward Fees', 'Forward Fees'[Consultant] = EARLIER( 'Master_Tbl'[MIS Name] ) )

VAR myEmp = CALCULATE(SUMX(FILTER(EmployeeRows, 'Forward Fees'[Consultant]<>BLANK()),'Forward Fees'[Future Fees]) )

RETURN

LOOKUPVALUE(

'Forward Fees'[Future Fees],

'Forward Fees'[Future Fees], myEmp,

'Forward Fees'[Consultant], 'Master_Tbl'[MIS Name]

)

This is where I'm going with it:

Master_Tbl

And the relationships:

[v-frfei-msft]

Hi @Multiverse_76 ,

Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[Multiverse_76 · Accepted solution]

@v-frfei-msft  Thanks for being so ready to help.  I've actually solved the problem.  Seems I overbaked the formula which works perfectly without the LOOKUPVALUE part.

Forward Fees =

VAR EmployeeRows =

FILTER( 'Forward Fees', 'Forward Fees'[Consultant] = EARLIER( 'Master_Tbl'[MIS Name] ) )

VAR myEmp = CALCULATE(SUMX(FILTER(EmployeeRows, 'Forward Fees'[Consultant]<>BLANK()),'Forward Fees'[Future Fees]) )

RETURN

myEmp

[Greg_Deckler]

You can generally use something like MAXX coupled with FILTER to replace LOOKUPVALUE and get a bit more control over what is happening.