Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
nagaraj
Post Patron
Post Patron

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 ID , i need to select the record where Type = MANAGER

 

Table 1

IdManager
1 
2 

 

Table 2 

IdnameType
1abcAssistant
1xyzManager
2bcdManager
2fghAssistant

 

Regards

Nagaraj

1 ACCEPTED SOLUTION

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]))

View solution in original post

7 REPLIES 7
MG86
Advocate II
Advocate II

I know this is an older thread, but for future reference isn't it easier to just use the second lookup parameters of LOOKUPVALUE?:

LOOKUPVALUE(
   'Table2'[Name], 'Table2'[Id], 'Table1'[Id], 'Table2[Type], "Manager")

this is the only correct answer !

v-lid-msft
Community Support
Community Support

Hi @nagaraj ,

 

We can try to create a calculated column in 'Table 1' to meet your requirement:

Manager =
CALCULATE (
    MAX ( 'Table 2'[name] ),
    'Table 2'[Type] = "Manager",
    'Table 2'[id] = 'Table 1'[id]
)

 


Best regards,

 

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-lid-msft 

 

 HI, 

 

 Thanks for the help, was able to resolve this using the below 

 

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

tctrout
Resolver III
Resolver III

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.

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]))

  • 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 ?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.