DAX Fridays! #41: LOOKUPVALUE with multiple columns

[@bradj229]

"You don't need a relationship between tables when using the LOOKUPVALUE function, but you do when using RELATED." - most important piece. Love it! Thanks Ruth!

[@CurbalEN]

Your journey continues!!

[@TheAlexandersword]

Very helpful! I wonder if they will incorporate the xlookup design into the a new DAX variant of LOOKUPVALUE.

[@miragliag]

Great! I wonder if it would have same result with concatenate dateandcompany and then merge query function

[@CurbalEN]

+Giovanni Miraglia Sure, that is what you would normally do in excel, Happy Friday ;) /Ruth

[@garymcbride340]

Hi, you said in the video that you never use this function, with one exception. Can I ask what do you use instead? I’m creating a calculated column within which my formula looks up the mortality rate from a second table for people born In the main tables with the same year of birth and multiplies this mortality rate to another column in the main table. Would you not use this lookupvalue to do that? Thank you, great videos.

[@SmartBimson]

Very interesting video Ruth ! And I'll give the dutchdatadude a thumbs up as well 👍🏻 and now enjoy your weekend and see you soon 😉🙋🏼‍♂️🌞

[@CurbalEN]

+SmartBimson Next Friday hopefully ;) Have a great weekend and saturday morning! I have a surprise for saturday Morning, let's see how that works out ;) /Euyh

[@CurbalEN]

+SmartBimson I can finally start my Friday now! ;) 👌 /Ruth

[@SmartBimson]

Curbal you do ? That's awesome Ruth, and again you have a curious ... 😉 actually your video helps a lot. I red already something about connecting tables and so on and his is a part of it ... and i hope next week my company will approve me a power bi license .. let's see let's see ;-) But now I'm really curious what's up for Saturday morning 🥂👍🏻

[@CurbalEN]

+SmartBimson I hope you get a license!!! Otherwise, tell me who I should speak to! I thought I had to disturb your Saturday mornings with another video ;) /Ruth

[@SmartBimson]

Curbal about the license I don't know .. usually the IT departments are so hysterical when I want to install something that is connected with a cloud ... and actually in our department we definitely have no company secrets at all. But that doesn't count ;-) But I already introduced myself to the head of IT in out group some weeks ago and maybe if there is a no now from the IT guys I can overrule their decision by convincing the big boss ;-) I'll keep you updated 🙃 but now enjoy your weekend as well 💃🏻🕺🏼

[@saadhanif4628]

I am making an HR dashboard in power bi for which i want to lookup the department column of each employee which was updated recently from the Main table. Please advise what formula will i use alongwith lookup dax

[@keerthanUpadhyaa]

Dear Ruth.. I recently started working on DAX and Power BI (since about 2 weeks) but all my friends and colleagues think that I have intermediate level.. Crazy right!? :) all the credit goes to you and your beautiful videos. You are doing a fantabulous job!! I feel you are definitely almost at the center of your Ikigai.

[@CurbalEN]

👏👏👏 That will give you super powers!! /Ruth

[@georgie3891]

Do you have a video on how to do this in a measure as opposed to a calculated column?

[@pabeader1941]

I know it's been a while since you did this video but I have a great use for this function. I have two tables that require the combination of two columns to make a unique selection. In my case both tables have Date and Machine. In each table, the Date, Machine combination identifies a unique row. By using Lookupvalue, I am able to get the value from table2 that I need as part of a calculated column in table1. Very much like your example for which I am grateful.

[@CurbalEN]

Thanks for sharing :) /Ruth

[@eduardoletayf6676]

Hi! You mentioned at the end that you haven't actually used lookupvalue that much... What alternatives do you recommend to bring values from other tables to the one currently being used? I need some options to do different calculations.

[@CurbalEN]

I nowadays use other functions for that, which ones depend on what I am doing so it is difficult to give more precise advice.

[@eduardoletayf6676]

@@CurbalEN thankyou! I’ll try to be more specific the next time cause I already solved the issue I had... fan #1 of your videos!

[@CurbalEN]

Thanks!

[@nikhilranjan939]

I have a table with some questions in column headers. Each row corresponds to one persons response . The answers to question are A, DA, SA,SD.. which corresponds to agree disagree strongly agree etc. I want to replace all short forms (A with agree) in all column at once. Is it possible? I have done one column at a time and it's time consuming. I have 20+ questions as columns.

[@sandraglove10]

Thank you so much! you helped me to understand the formula and apply it into my data model. DAX is very hard for a dummie like me, Please don't stop uploading videos♥!

[@CurbalEN]

We are all dummies when doing DAX ;) /Ruth

[@raphaelanzalone6190]

Hello I began to use Power BI some weeks ago. I have a table with columns named "Costumer", "Check-in", "Check-out" and "Paid value". Sometimes, the costumer decides to remain some days more. In this case, for the same costumer, the date in Check-out column is equal to the date in Check-in column in diferent rows. How do I do to sinthetise that rows (sum the paid value, maintain the name Costumer, modify the check-out as the real check-out date, and exclude the row that contains the informations about this days more). Ideally I would like to do that in Power Query Thanks

[@officesuperhero9611]

EXACTLY what i was looking for. MIL GRACIAS!!

[@CurbalEN]

🥳

[@abhishekstatus_7]

It is really great Ruth ! i am having same kind of situation , i tried to achieve this by looking this scenario but its not working . I have profit center in another table , cost center in another table and two different measures as well from both the tables. In these both table Segment Column is also available for which i created calculation Segment = UNION(DISTINCT(FAGLFEXT[SEGMENT]),DISTINCT(BSEG[SEGMENT])) as a new table and got the unique Segments into one column. Now the challenge is Profit center and Cost center because it is possible that for one Segment there can be multiple profit center and cost center. Could you please help me how to fix this..

[@luisalejandrorodriguezcamp9516]

Hi Ruth Is it any way to do a LOOKUPVALUE or something similar in DAX to get values between dates. i.e =LOOKUPVALUE( [Desired Column], [Dates column1],">"&01/01/2018, [Dates column1],"

[@CurbalEN]

Hi Luis, a calendar slicer does not work for you? Could you post the question in the power BI community? Make sure you give details of what you are trying to accomplish! /Ruth

[@daniel.balandra]

@@CurbalEN Hi Ruth, but lets say, in the video example, you have your stock value table in a "range of dates", where you have two columns, start date and end date, so in this case how could look for the stock value in the market share table? The usual case where we possibly need this for me is when i have "price list" table of products, and sales fact table. So we want pull the price to sales table. How could i do this?

[@CurbalEN]

Hi Daniel, Post an example of your scenario in the power bi community to get help quickly :) /Ruth

[@srutis4453]

I have a query... I need to plot incidents monthly based on x asix for open, closed, new incidents where open, new have been taken from created date and closed incident has been taken from closed date.. How do u plot all these open, new and closed with monthly based.... Note : here monthly I have 2 columns one is created and one is closed how to take one month basis and plot above three parameters in powerbi...

[@e-pardes2344]

For example, I have in stock an extra company name (Let's say D) which is not in the Market share company, But I want the result of D in my overview as 0.? What do I need to do in this case? I am compairing my forecast data with actuals, so some customers are in actuals which are not in forecast data & vice versa. Please help?

[@youbetzler]

I'm trying to use lookupvalue to bring a "removal date" from one table to another. Both tables have a column "batch" which is a many to many relationship. I need to work out days elapsed between the removal date of a component and the repair order create date. But the data set has these two values in separate rows so i used excel filters to essentially make two seperate tables. One for the removal transaction and one for the repair order create transaction. Both values are linked by the batch but when i use the DAX formula I get the error message "A table of multiple values was supplied where a single value was expected." i've been looking for a solution for almost 2 weeks now and getting nowhere and my boss is on my case to get this report going but I can't figure this out. Any ideas?

[@ViewerBot300]

You are a POWER BI GODDESS! Thank you! THank you! THANK you !

[@CurbalEN]

OMG, best comment ever 😂😂 You really made me laugh (obviously in a very positive way), thanks!!! /Ruth

[@abdulmuminiabdulkarim6233]

Hi, can we achieve same output in Power Query using relationships with conversion table followed by a Group-by function?

[@mohammedshaheel]

@Curbal. Great video. Very useful. I have one suggestion. Can you show a video on how to sort a month as Jan, Feb etc rather than in alphabetical order.

[@CurbalEN]

+Shaheel Khan Hi Shaheel, i show that in my calendar videos, have you seen it? /Ruth

[@mohammedshaheel]

@Curbal. I have seen your calendar videos and I have also created Date Table as shown by you in that video. Perfect. But my problem is when I put Month in the slicer it is not in Jan, Feb etc format rather it is in alphabetical order starting from April, August etc. How to solve it?

[@CurbalEN]

+Shaheel Khan Hi again, In this video: m.kzbin.info/www/bejne/rXnRamlum5Z4a5o I show how to that. It is around minute 06:50. /Ruth

[@mohammedshaheel]

@Curbal. It was awesome. Thanks a lot for helping me. As I am using PowerBI at my office and I am also new to this tool, I might trouble you with similar doubts. Hope you will help me and many as well...

[@CurbalEN]

+Shaheel Khan I will definitely try! For bigger inquiries the power bi community is your best bet ;) /Ruth

[@arahanthajain4282]

hi, can we use Lookupvalue within same table?

[@CurbalEN]

Hi, I dont think so.

[@arahanthajain4282]

@@CurbalEN thank you

[@meryemmmm3874]

can we get multiple result columns for matching records?

[@genaugenaugenau]

Superbly explained, thanks Ruth!

[@figarocalisthenics9516]

Can I ask you a question? I have an excel sheet which I pull out every day and import in PowerBI. I never save the old files but just keep the newest. Some days ago I found out that one of the columns was removed some time ago. I looked in my mail and found a copy of this file with all the columns until the 15th of may. I also found another system where I can pull out a sheet with the missing column from the 15th of May until now. I can not pull the file complete, so I need to merge three files into one. The main file missing the column, the file containing the column until the 15th of May, and the last file from the 15th until now. The common denominator in all three files is the ticket ID. My concern is that all the three files contain different number of tickets but at the same time many of the tickets are the same.

[@CurbalEN]

You could do a merge to add only unique rows from all the tables, have you tried that? /Ruth

[@figarocalisthenics9516]

I thought I could use Relationships, or is it not possible?

[@CurbalEN]

Absolutely, you can do that too. /Ruth

[@figarocalisthenics9516]

My problem is that I am too green in this. Could you make a quick tutorial? Three work books, or maybe one workbook with three tabs, one for the complete list minus the subject column. A second tab with the sheet which contains is only updated until the 15th of May but that contains the subject column, and the last tab which contains the two rows one of them being the Subjects, and from the 15th until to day. How to do this?

[@CurbalEN]

Oh, I understand. Can you please post the question in the power bi community? Post as much detail as you can to get help? /Ruth

[@huynguyenquang9964]

I don't know why but I couldn't find this LOOKUPVALUE function. Did I do anything wrong? I'm using Power BI version "(2.76.5678.782 64-bit (December 2019))

[@CurbalEN]

Weird, no idea! Post here issues.powerbi.com /Ruth

[@sayanbiswas9902]

Can you please tell me how to lookup a single column from a different table and matching it with the single column of another table?

[@daweiwang3265]

Thank you so much, you just saved my day! 非常感谢！🎉

[@CurbalEN]

🥳

[@nsr6]

Good one. Tried the scenario 1 in a different way. created a new column as JKEY in both tables as compound key. JKEY = MarketShare[Company]&"-"&FORMAT(MarketShare[Date],"yyyymmdd") and JKEY = StockValue[Stock]&"-"&FORMAT(StockValue[Date],"yyyymmdd") and created the relation ship on JKEY. (1:1). We can add multiple columns in jkey to make it unique and can be joined. Not sure why they limited the relationship to only a single column (:

[@CurbalEN]

Thanks for sharing :) /Ruth

[@saltsea9499]

how can I aggregate to a single value when I have multiple values returned?

[@excelisfun]

Thanks for the LOOKUPVALUE fun!!! : )

[@CurbalEN]

+ExcelIsFun You welcome :) Lookupvalue is best used with row level security. I just have to find the time to do the video! Happy Friday 🌞 /Ruth

[@excelisfun]

Happy Friday to you : ) ... BTW, my Friday would be just a bit less happy if you had not made and posted your DAX Friday video: it bit of sunshine each Friday : )

[@CurbalEN]

+ExcelIsFun It is? Thanks, it means a lot! 😊 I wonder what I will do once I work out the entire function list, microsoft needs to create new ones ....or I can start all over again. I am sure I can explain them better now than when I started out. Progress.. /Ruth

[@excelisfun]

Yes, of course it is : )

[@excelisfun]

What, an analyst like you!?!?! The list of topic is nearly infinite... The combinations and permutations from the Microsoft tools mixed with all the applications in all the different fields: that number is huge : ) You will NEVER run out of topic : )

[@kevinlabore1726]

Thank you. This is just what I needed.

[@CurbalEN]

🎉 /Ruth

[@akashragi2347]

Hey can you please reply me 1. How to change the colors for each country in shape map? Example : I have 5 different countries that has readiness percentages .The map should show colors that percentages between 90

[@CurbalEN]

+akash ragi Hi slash, Please post your question in the power bi community blog, I am unable to answer all individual requests. They will be able to help you there, /Ruth

[@hecdapalo]

Hi Curbal!, i love your videos, you have helped me with big ones! i know this video is old but i haven't been able to find the answer for this little issue i have, is there a way i can use LOOKUPVALUE with something similar to a wildcard (*) ? I have three column that i need to use to find the correct match but one of them doesn't have the complete text so LOOKUPVALUE won't match it. thank you so much again and i hope you or someone else here can help me with this.

[@CurbalEN]

Don't know, maybe this can give you ideas? m.kzbin.info/www/bejne/rJO0k4R4jZeHbqM Have you tried power query?

[@kummithavenkatareddy2302]

Can you explain the execution process?

[@julysantaschmus627]

Thats so great! You know for what scenario i needed it? For Stock Value of different companys : ) That solves it perfectly.. many many thanks! the best source to get solutions for anspecific dax problem = your channel!

[@CurbalEN]

🥳🥳 Enjoy your weekend ! /Ruth

[@feng3625]

please correct me, I always think the lookup value like join in power query.

[@CurbalEN]

Lookup is like a vlookup in excel. /Ruth

[@feng3625]

@@CurbalEN I don't know excel. Is this sort of join for 2 tables?

[@CurbalEN]

Feng it looks up a value that exists on another table. /Ruth

[@loginraj]

very helpful.. it really made my day..

[@mathijs9365]

great tutorial.This you can use to dynamic calculate fgures in selected currency WITH date.

[@CurbalEN]

+Mathijs Great! I thought you would like it! ;) /Ruth

[@michelleblackwell6590]

I keep getting "A table of multiple values was supplied where a single value was expected" error, how do I fix this?

[@TheAlexandersword]

Did you review the relationships of your tables to be synonymous to the schema she uses in the attached example?

[@ericbrenner690]

Thank you! Very well explained.

[@CurbalEN]

My pleasure!

[@neelkanthbk]

Thanks a ton for the wonderful video :)

[@djeritorres]

Excelente... Me leíste el pensamiento, es lo que estaba buscando ahora, voy a ponerlo en práctica. Gracias

[@CurbalEN]

+djeritorres Genial!! Muchos saludos y gracias por ayudar en los comentarios ;) /Ruth

[@djeritorres]

Curbal No hay de que. Así como tú compartes tus conocimientos, hay que hacer lo mismo. Dj

[@CurbalEN]

+djeritorres Muchas gracias, aprecio la ayuda. Es difícil contestar a todos, siempre me falta tiempo! /Ruth

[@djeritorres]

Curbal Si me imagino, de igual forma te tomas el tiempo en responder y eso es mucho. Si supiera inglés podría apoyar más, por lo pronto no sé mucho, veré si Google Traductor ayuda en algo, Jajajaja...

[@CurbalEN]

+djeritorres No te preocupes, hay muchos comentarios en español 😄 /Ruth

[@Franaman]

brilliant as always!!!!

[@CurbalEN]

Ohhhh, thank you 😊 /Ruth

[@JaredTheStrange]

Really great! I was having errors using this and you led me to find a problem in my search value column. :)

[@MrJccastro79]

Great example! Many thanks

[@CurbalEN]

+jose augusto camacho castro Thanks José! Make sure you give a thumbs up to the Dutchdatadude also! Have a great weekend! /Ruth

[@ianelder1743]

Another problem solved - thanks Ruth.

[@CurbalEN]

My pleasure :) /Ruth

[@jacquelinelacan]

Back for some lindo DAX, thank you once again Ruth!

[@CurbalEN]

Here to serve ;) /Ruth

[@VKASBHAKTICHANNEL]

you have a great knowledge

[@CurbalEN]

Thanks :) /Ruth

[@mehidavera3069]

Thanks for this.

[@CurbalEN]

You welcome Mehida! /Ruth

[@kapilkumarasamy5059]

worked like a charm.... thank you so much :)

[@CurbalEN]

You welcome :) /Ruth

[@dinethprabash1001]

This solved my problem. Tx

[@CurbalEN]

Glad to hear! /Ruth

[@shivanihandore4738]

Thanks 😇

[@CurbalEN]

😇

[@mynameisyuriorlov]

perfect Curbal, as always ;) Augustin de la Fouchardiere

[@CurbalEN]

Thanks Agustin! :) /Ruth

[@artemburov9456]

Thank you!

[@CurbalEN]

You welcome! /Ruth

[@ShinCody]

Phenomenal... Thank you

[@shashankghh99]

Thank you very much for this video ! Can you please make a video on the Row Level Security using #LOOKUPVALUE ?

[@CurbalEN]

Added to my list! /Ruth

[@rrrprogram8667]

Yeah... i am progressing with DAX noww.

[@CurbalEN]

+RRR program Oh yeah! Cool!! :) /Ruth

[@SelfiePalace]

thankyou

[@CurbalEN]

My pleasure :)

[@SelfiePalace]

I'm struggling with this lookup function in my project. This helps me a lot. Thankyou 😀

[@CurbalEN]

Music to my ears!!! /Ruth

[@Zzbalex]

si agregaras subtitulos en español abarcarías toda latinoamerica

[@CurbalEN]

+Zabalex Si, Es cierto. Solamente necesito un poco de tiempo para hacerlo, pero es muy buena idea :)

[@hammoudinedjare2204]

Wow, you're Ammmizing............

[@CurbalEN]

Thanks :)

[@edoedikson9560]

Thank you!!!!

[@CurbalEN]

My pleasure:)