Forum Discussion

Creative_tree88's avatar
Creative_tree88
Icon for Helper V rankHelper V
3 years ago
Solved

Lookup Value based on a specific field

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 KeyA or D
15765116Activity
15765116Demand
10579661Demand
10579662Activity
16955865Activity
16955866Demand
17313760Activity
17412241Activity
17412242Demand

 

Hosp No? = LOOKUPVALUE('Activity & Demand'[AD_Hosp_Number], 'Activity & Demand'[AD_Event_Key],'Cancer Performance Data'[Event key], "Unknown")
 
Many thanks!
Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    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.

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

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

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    3 years ago

    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.