Forum Discussion

nagaraj's avatar
nagaraj
Post Patron
6 years ago
Solved

LookupValue with Filter

Hi,    Need help with DAX for below scenario   Scenario: Have TABLE1 and TABLE2, need to populate manager (in TABLE1) by having lookup (in TABLE2). In TABLE2 i have multiple values for a single ...
Need Help
  • nagaraj's avatar
    nagaraj
    6 years ago

    Hi tctrout 

     

    Thanks for the help, was able to resolve using below 

     

    Property_Manager = CALCULATE(FIRSTNONBLANK(Table2[Name],1),
    filter(all(Table2),
    Table2[Type] ="Property Manager" &&
    'Table1'[Id] = Table2[Id]))

tctrout's avatar
tctrout
Responsive Resident
6 years ago

The approach I would explore in M Code, not DAX.. This can be done within the GUI interface

 

Create copy of Table 2

Filter for Manager only

Remove duplicates on ID, if eixists.  This should provide you a table of manangers, their names and ID

 

Go to Table 1 and merge table 2 using ID as the key/lookup value.

  • nagaraj's avatar
    nagaraj
    Post Patron
    6 years ago

    Hi tctrout 

     

    Thanks for the help, was able to resolve using below 

     

    Property_Manager = CALCULATE(FIRSTNONBLANK(Table2[Name],1),
    filter(all(Table2),
    Table2[Type] ="Property Manager" &&
    'Table1'[Id] = Table2[Id]))

    • Gokul_G16's avatar
      Gokul_G16
      Resolver I
      3 years ago
      • Thanks nagaraj
      • That is working fine
      • But have one concern while we used FIRSTNONBLANK()?
      • CAN U PLZ LET US KNOW THAT
      • What are the secnario we do it ?