cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
domdom Helper II
Helper II

Vlookup in power bi - please help :)

Hi there,

 

I'm trying to replicate something similar to a vlookup in power bi (I've checked the other questions on this but haven't found the right answer yet so hoping you can help!)

 

I have the following table:

 

value                id            date

Yoyo301 February 2015
Dog315 March 2015
Willing110 May 2015
Caz306 July 2015
Alain314 July 2015
Leppard101 August 2015
Zoooo301 August 2015
Hello429 August 2015
Tastycakes208 November 2015
Venture411 November 2015
Scarborugh111 January 2016
Supermaker208 March 2016
Special120 March 2016
Behold102 May 2016
AandE324 June 2016
Pierre109 July 2016
Wetsuit125 July 2016
To320 August 2016
Tasty402 November 2016
World415 November 2016
Unknown123 March 2017
Rahrah426 March 2017
   

 

I want to add in a calculated column that will return the first found value in the table given an id.  E.g. the table how it is at the moment (sorted descending by date) - if I looked up 1 - I would want it to show "Willing" which is the first found value for id 1.

Similarly - if the id was 2 - then it should be Tastycakes.

 

However - if I was to sort this table in date order ascending instead - I would want the function to update.  If it was ascending for id 1 this should match "Unknown" first.

 

Hope that makes sense?

 

I'm happy for the sorting to be done inside DAX instead of in the query if thats easier....

 

Many thanks for any help you can give me

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Vlookup in power bi - please help :)

Hi,

 

I cannot change the results with sorting but cal get your desired result in 2 seperate columns.  Try these calculated column formulas

 

Value on first date=LOOKUPVALUE(Data[Value],Data[Date],CALCULATE(FIRSTNONBLANK(Data[Date],1),FILTER(Data,Data[ID]=EARLIER(lookup_ID[ID]))),Data[ID],[ID])

 

Value on last date = LOOKUPVALUE(Data[Value],Data[Date],CALCULATE(LASTNONBLANK(Data[Date],1),FILTER(Data,Data[ID]=EARLIER(lookup_ID[ID]))),Data[ID],[ID])

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Super User IV
Super User IV

Re: Vlookup in power bi - please help :)

Hi,

 

I cannot change the results with sorting but cal get your desired result in 2 seperate columns.  Try these calculated column formulas

 

Value on first date=LOOKUPVALUE(Data[Value],Data[Date],CALCULATE(FIRSTNONBLANK(Data[Date],1),FILTER(Data,Data[ID]=EARLIER(lookup_ID[ID]))),Data[ID],[ID])

 

Value on last date = LOOKUPVALUE(Data[Value],Data[Date],CALCULATE(LASTNONBLANK(Data[Date],1),FILTER(Data,Data[ID]=EARLIER(lookup_ID[ID]))),Data[ID],[ID])

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

domdom Helper II
Helper II

Re: Vlookup in power bi - please help :)

Thank you I have got this to work 🙂

domdom Helper II
Helper II

Re: Vlookup in power bi - please help :)

Hi again @Ashish_Mathur

 

Actaully - this throws an error of "A table of multiple values was supplied where a single value was expected" if there is a duplication of ID and Date in the table - e.g.

 

valueiddateValue on first date

Biscuit114 July 2017#ERROR
Tasty114 July 2017#ERROR
Cakes214 July 2017#ERROR
Mr Man214 July 2017#ERROR
Pierre314 July 2017#ERROR

 

In this instance - it doesnt matter which value is looked up (e.g. for 1 can be either Biscuit or Tasty - whatever it finds first) - but it just needs to not error!

 

any chance you can help fix that?

 

Thanks

Super User IV
Super User IV

Re: Vlookup in power bi - please help :)

Hi,

 

You may refer to my solution in this PBI file.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors