Forum Discussion
LOOKUPVALUE based on 2 variables issue
Hi all,
I'm trying to get a value (Team) from one table (Team List) to pull through to a new column in a second table (Target List) based on two columns within both tables (Name and Month) which should match.
A rough sample of the data is as follows:
Team List
| Name | Month | Team |
| Ryan | Jan | 1 |
| Ryan | Feb | 1 |
| Ryan | Mar | 1 |
| Ryan | Apr | 1 |
| Ryan | May | 1 |
| Ryan | Jun | 1 |
| Ryan | Jul | 2 |
| Ryan | Aug | 2 |
| Ryan | Sep | 2 |
| Ryan | Oct | 2 |
| Ryan | Nov | 2 |
| Ryan | Dec | 2 |
Target List
| Name | Month | Target | Team |
| Ryan | Jan | 10000 | |
| Ryan | Feb | 10000 | |
| Ryan | Mar | 10000 | |
| Ryan | Apr | 10000 | |
| Ryan | May | 10000 | |
| Ryan | Jun | 10000 | |
| Ryan | Jul | 10000 | |
| Ryan | Aug | 10000 | |
| Ryan | Sep | 10000 | |
| Ryan | Oct | 10000 | |
| Ryan | Nov | 10000 | |
| Ryan | Dec | 10000 |
I have tried the following DAX using the LOOKUPVALUEFUNCTION but, although doesn't return an error, isn't pulling through the correct data:
Team = LOOKUPVALUE('Team List'[Team],'Team List'[Name],[Name],'Team List'[Month],[Month])
That is giving me the result of:
Target List
| Name | Month | Target | Team |
| Ryan | Jan | 10000 | 1 |
| Ryan | Feb | 10000 | 1 |
| Ryan | Mar | 10000 | 1 |
| Ryan | Apr | 10000 | 1 |
| Ryan | May | 10000 | 1 |
| Ryan | Jun | 10000 | 1 |
| Ryan | Jul | 10000 | 1 |
| Ryan | Aug | 10000 | 1 |
| Ryan | Sep | 10000 | 1 |
| Ryan | Oct | 10000 | 1 |
| Ryan | Nov | 10000 | 1 |
| Ryan | Dec | 10000 | 1 |
Why is it not picking up that Team should be '2' from July onwards? Very confused by this and not sure where I'm going wrong.
9 Replies
sure I can try to look at it.
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
dom-mitchell is there any relationship between these tables?
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
There is a relationship via third table which needs to be there to make soemthing else in the report work.
dom-mitchell and if there is a relationship, delete the relationship and then it should work.
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
I've now tried it with no relationship between the tables and it still isn't pulling the values thorugh correctly. Same result as with the relationship.
dom-mitchell what is that relationship? Is that relationship with both the tables? Please provide more details on the model, it is not something you are doing wrong using lookupvalue it is the relationship which is causing the issue and without having full details it is hard to provide any solution.
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
Thanks for the help so far parry2k .
I've attached a screenshot of the relationship view.
I'm tempted to upload the pbix but it contains significant company data which is a worry for me to put up here.
dom-mitchell I just did with your sample data and it works, see attached Power BI file.
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
Yeah, it looks to be the same as mine but for some reason, mine just doesn't pull the data through correctly.
Could I email you the PBIX? Don't really want to put it up here for download.
