How to retrieve a value from unrelated table based on a criteria

[semsel]

 I have a standalone table which can't be related to any other tables, It shows the Duration of each activity is suposed to take.

Activity Duration

ActivityName | Est Duration(hrs)

Item Creation | 2

Quote Creation | 10

Customer Creation | 5

I have different tables for Items, Quotes and Customers and then a User table.

User table is related to Quotes, Customers and Items using the CreatedUser link, I also have a date table that is linked to Create dates of each table that is used in a slicer

In User table I want to calculate how many hours he took for each action on a particular date.

I am able to find no of items the user created on a particular date using this formula:

#Items Created : CALCULATE (COUNT (Items[ItemID])

My Matrix Looks like:

UserName | Hours for Item Creation | Hours for Customer Creation ....

But how do I multiply this by duration for Item Creation (which is 2 for ActivityName = "ItemCreation"). I want to dynamically retrieve 2 as there are lot of such defined actions and i don't want to hardcode it.

I tried using LookuPVALUE function but it requires something to be matched with the current table.

[v-angzheng-msft]

Hi, @semsel 

It's hard for someone to know your data model and what you want to do just from the above text description.

Could you please consdier sharing some dummy data and posting expected result so it is clear on what needs to be implemented? 

And It would be great if there is a sample file without any sesentive information here.

As a general advise, please, provide:

1. Sample (dummy dataset) data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Best Regards,
Community Support Team _ Zeon Zheng

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