Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Yoyo | 3 | 01 February 2015 |
Dog | 3 | 15 March 2015 |
Willing | 1 | 10 May 2015 |
Caz | 3 | 06 July 2015 |
Alain | 3 | 14 July 2015 |
Leppard | 1 | 01 August 2015 |
Zoooo | 3 | 01 August 2015 |
Hello | 4 | 29 August 2015 |
Tastycakes | 2 | 08 November 2015 |
Venture | 4 | 11 November 2015 |
Scarborugh | 1 | 11 January 2016 |
Supermaker | 2 | 08 March 2016 |
Special | 1 | 20 March 2016 |
Behold | 1 | 02 May 2016 |
AandE | 3 | 24 June 2016 |
Pierre | 1 | 09 July 2016 |
Wetsuit | 1 | 25 July 2016 |
To | 3 | 20 August 2016 |
Tasty | 4 | 02 November 2016 |
World | 4 | 15 November 2016 |
Unknown | 1 | 23 March 2017 |
Rahrah | 4 | 26 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
Solved! Go to Solution.
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.
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.
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
Biscuit | 1 | 14 July 2017 | #ERROR |
Tasty | 1 | 14 July 2017 | #ERROR |
Cakes | 2 | 14 July 2017 | #ERROR |
Mr Man | 2 | 14 July 2017 | #ERROR |
Pierre | 3 | 14 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.
Thank you I have got this to work 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |