LOOKUPVALUE based on 2 variables issue

[dom-mitchell]

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. 

[parry2k]

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.

[parry2k]

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

[dom-mitchell]

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. 

[parry2k]

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

[dom-mitchell]

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. 

[parry2k]

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

[dom-mitchell]

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. 

[parry2k]

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

[dom-mitchell]

There is a relationship via third table which needs to be there to make soemthing else in the report work.