Lookup Value based on a specific field

[Creative_tree88]

Hi - I am using the following formula to look up AD_Hosp_Number in the table 'Activity & Demand'.  I'm using the corresponding value in the Cancer Performance Data table as my lookup.

However, in the activity and demand table, there are event keys for both activity and demand, so I want to somehow find a way to only lookup the values which correspond only those rows which are activity.  An example of this is below.  So, you can see some of the event key values have both activity and demand, but I only want to lookup those relating to activity:

Event Key	A or D
15765116	Activity
15765116	Demand
10579661	Demand
10579662	Activity
16955865	Activity
16955866	Demand
17313760	Activity
17412241	Activity
17412242	Demand

Hosp No? = LOOKUPVALUE('Activity & Demand'[AD_Hosp_Number], 'Activity & Demand'[AD_Event_Key],'Cancer Performance Data'[Event key], "Unknown")

Many thanks!

[v-zhangti · Accepted solution]

Hi, @Creative_tree88 

You can try the following methods.

Column = 
CALCULATE ( MAX ( 'Activity & Demand'[A or D] ),
    FILTER ( 'Activity & Demand',
        [Event Key] = EARLIER ( 'Cancer Performance Data'[Event Key] )
            && [A or D] = "Activity"
    )
)

Is this the result you expect? If not, please provide more details.

Best Regards,

Community Support Team _Charlotte

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

[amitchandak]

@Creative_tree88 , Try a new column like

coalesce(
max(filter('Activity & Demand', 'Activity & Demand'[AD_Event_Key],'Cancer Performance Data'[Event key] ),'Activity & Demand'[AD_Hosp_Number]), "Unknown")