Forum Discussion

Multiverse_76's avatar
Multiverse_76
Frequent Visitor
6 years ago
Solved

LOOKUPVALUE pulling through blanks

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:

 

Need Help
  • Multiverse_76's avatar
    Multiverse_76
    6 years ago

    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

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

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

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    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's avatar
      Multiverse_76
      Frequent Visitor
      6 years ago

      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