Looking for value in 2nd table based on the value of 1st table

[al1981]

Dear all

I would kindly ask for help...

I have list of event attendees, and for each attendee there is quater plan/target (A,B). In most of the cases, we are looking for the plan value of the attendee, but in same cases, if this attendee is manager, we would like to get value for its parent (company). What would be best scenario? This bellow is very simply table, just to see the situation. Would be possible somehow to get me out? 

[Anonymous · Accepted solution]

Hi @al1981 

Please try using LOOKUPVALUE() function to create a new column. Hope it helps.

Plan Value = 
IF(
    'Event attendees table'[Attendee Type] = "Worker",
    LOOKUPVALUE('Plan Table'[Plan], 'Plan Table'[Account ID], 'Event attendees table'[Account ID]),
    LOOKUPVALUE('Plan Table'[Plan], 'Plan Table'[Account ID], 'Event attendees table'[Attendee Parent])
)

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[al1981]

Dear @Anonymous 

many tnx for this info! It is not exacly going into my data model (many tables behind, including AllAccounts - next to attendees table, plan table), but i dupicated Event attendees table, for Attendee name and Attendee ID i replaced with Parent name and Parent ID and of course removed duplicates and some other unusefull columns! All works like a charm now - but your help really inspired me how to turn this arround ! Many tnx!

[al1981]

Dear @Anonymous 

and your lookupvalue is very good some other project i have - so many tnx  anyway!

[Anonymous]

Hi @al1981 

Thank you for your kind words. 

If my reply helps you, please consider acceptng it as the solution. This will help others who might encounter similar problems benefit from the information as well.

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bhanu_gautam]

@al1981 , Ensure that the tables are properly related. Typically, you would have relationships like:

"Event Attendees table" linked to "Plan Table" via an attendee ID or similar key.
"Event Attendees table" linked to "Date Table" via a date key.

Add a Calculated Column: Create a calculated column in the "Event Attendees table" to determine the plan

value based on whether the attendee is a manager or not.

DAX
PlanValue =
IF (
'Event Attendees table'[IsManager] = TRUE,
RELATED('Plan Table'[CompanyPlanValue]),
RELATED('Plan Table'[AttendeePlanValue])
)

[al1981]

Dear  @bhanu_gautam 

almost all is clear, jsut plan table not: i have worker and company name in same column, same goes for plan. Is there any change to keep it like this as i am using this model for other prepared reports already...

I would like someting like this: if Event attendee is Manager then look for Plan value for Attendee Parent in the plan...