topic Re: Looking for value in 2nd table based on the value of 1st table in DAX Commands and Tips

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

al1981 — Thu, 29 Aug 2024 13:34:20 GMT

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?

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

bhanu_gautam — Thu, 29 Aug 2024 14:05:34 GMT

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

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

al1981 — Fri, 30 Aug 2024 10:42:58 GMT

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...

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

Anonymous — Mon, 02 Sep 2024 03:46:35 GMT

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.

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

al1981 — Mon, 02 Sep 2024 11:18:37 GMT

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!

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

al1981 — Mon, 02 Sep 2024 11:20:05 GMT

Dear Anonymous

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

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

Anonymous — Tue, 03 Sep 2024 07:47:34 GMT

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.