Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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
Ashish_Mathur
Super User
Super User

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
Ashish_Mathur
Super User
Super User

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/

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

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/

Thank you I have got this to work 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.