Replicating Vlookup in power bi

[Anonymous]

Basically I have two tables Sales and District and both tables contain a Username column but I cannot create a relationship between them as it would be a many to many relationship and other reasons in my real report.

I want to get a district column on my Sales table so I would be able to get the sales per district. I could achieve this in Excel using vlookup. I tried using lookupvalue in power bi but was met with this error.

What can I do in this situation to get the District column in my Sales table?

Here is the sample pbix

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but I have one question.

In Sales Table, when user name is 111, what District you want to see? 

Korea? US? Germany? Jakarta?

When I see in Dist Table, under the username 111, there are more than one District name.

If there is any other factor to look-up, for instance, 'date' or 'a range of the sales amount', please kindly advise.

Thanks.

[Anonymous]

@Jihwan_Kim 

Sorry for not being clear. I'll just make my question as simple as possible. In this kind of situation how can I get the sales per district?

[Kumail · Accepted solution]

@Anonymous 

Sales is by username and for each username, there are multiple districts in a separate table. In order to relate sales of each user to a specific district you would need a key to join the 2 tables "Sales" and "District". This seems missing at the moment. 

Hope this helps.

Regards

Kumail Raza

[Kumail]

@Anonymous 

Lookup value should return error because Dist username have multiple district with same username.

If you are looking to extract district from district table in sales table, then you can use FirstNonBlank.

Dist = 
CALCULATE(FIRSTNONBLANK(Dist[District], TRUE()), FILTER(Dist, Dist[Username] = Sales[Username]))

https://drive.google.com/file/d/1rA3GjHmVUmeLjtILb4LerTTrqG9PYcRV/view?usp=sharing

Hope this helps!

Regards

Kumail Raza

Did this help? Kudos are appreciated!

Consider Accept it as the solution to help the other members find it more quickly