DAX for Power BI - (VLOOKUP) Search if Text Contains Value from Another Table
HTML-код
- Опубликовано: 8 сен 2024
- In this tutorial, I show you how to perform a VLOOKUP on your Power BI columns. We can take a text string from one table, and search for it in another. This is useful in case you need to pair text from different tables for any reason.
Enroll in my introductory or advanced Power BI courses:
training.bieli...
Elite Power BI Consulting:
bielite.com/
Data Insights Tools:
www.impktful.com/
When this was posted two years ago, I hadn't started working with Power BI. This trick comes to my rescue now! 😁
Many Thanks
I have been looking for this for quite a while myself. Thank you for this.
No problem, Bill!
BI Elite This was great. I spent hours looking for this I truly appreciate the time. However, I think I need one more step. How do I tell it to only return the first result found in the Key table e.g. Brown and Quick are found in one field. Dog is found first so the MatchedKey field only show brown.
The way you explained - awesome!! great work!!
Such a helpful video. As someone new to BI and DAX I had been struggling with how to do this but not anymore. Thank you.
Great to hear, Jemma! Thanks for watching
Very unique approach. Keep going.
Excellent tutorial Parker! Keep up this fantastic work!
Thanks Cecilio!
You saved my life, thanks for the detailed explanations (y)
Really glad to hear, Yassine!
This was extremely helpful, thanks - solved a problem I was trying to work through!
Glad to hear that, Christopher!
this was exactly what i was looking for. Thank you so much for posting this.
Glad to hear, Vipin!
Thank you so much, This is really helpful. Like all, I am looking for it for a long time.
Can someone please also help me, to select the keyword dynamically from a filter (including multiple keyword selections) and perform the same operation?
Great video, and my first intro to Dax. I implemented this script for myself and it worked - is there a simple addition I can make to add a space or comma (some kind of delimiter) between 'dog' and 'quick' when they appear together in a cell?
Hey man, I wrote the same code as you to get the difference of two columns. The difference in my case is that i compare ip addresses formated as text. The problem is sometimes i get more than one ip address back. For example i search for 10.10.210.99. It should always give back "not found" but in my case it gives 10.10.210.9 back and that ip address isnt the same. Sorry for my bad english. I would very happy if you answer me.
Anyway your video is very nice and helped me a lot with my problem. Keep going.
Hey Julian, just found your comment. It was in "Likely Spam" probably because of the IP addresses. If you haven't already solved your issue, I think you can get past it by using the LOOKUPVALUE function. This requires the full string in its entirety to be matched instead of if the text contains (which is what looks like is happening for you). Let me know if you figure it out!
Brilliant, it helped in my assignment .Kudos to you
Hello BI Elite… this video is super clear and very useful.
I’d be grateful if you could help with one more condition: NOT to return “dogs” based on “dog”. In my specific case when I search for “MAN” the tool returns “MANUFACTURING” and “GERMANY”. Could you please explain how to avoid that? Thanks
you are a legend ! thanks for the great help
No problem Leon!
Really nice
Thanks for this very useful tip :)
Tkz BI Eilte.
Just amazing! This was exactly what I was looking for. Thanks a lot
Extremely helpful video.. Something i was really looking for.
Glad to hear that, Shajeer!
Hey man, as always, great video. Yesterday I was doing something similar in excel, but this video makes want it to try in power bi as well. Th diffrence on my case is that I need to look up for a text string not in one column but in two, which will need to be be concatenated to account for both. Also, I believe the seach function is case sensitive in power bi, right? In which case, I would need to use FIND instead to account for both lower case and upper case.
Sounds like a fun problem. So are you testing if a string is in both columns? And if so, return something? I'd be interested to hear about the solution you come up with. I think you can use SEARCH though because in this video the Dog key matched the word dog in the searched column. But still, you might be right that FIND will work better for your case
Good stuff
Thanks Oscar!
Simple and elegant! Thanks!
Good afternoon. I just ran across your video and it has helped me get farther than anything I have found so far. However, what I need to do is identify EVERY instance of my text string I'm searching for. For example, if the text field contained "Happy Happy Birthday" and I was searching for "Happy", then I need it to return for BOTH instance of Happy. Is that possible? Thanks much! :)
I want to compare address columns in two different Excel books and return another column in one of those books if the addresses match slightly.
Are you trying to do this in Power BI? I would recommend creating a calculated column using the LOOKUPVALUE function. Possibly the SEARCH function
@@BIElite I would like to create it in Power BI. My experience has been that if the addresses differ even slightly, it doesn't find the correlation even if tables are joined with address being the key field. Thanks
I managed to do it but there is some extra work. Basically I created a third Excel workbook with two columns. Here I matched addresses manually and Power BI straight away picked up the results I wanted. Didn't have to use any functions; may be the functions can automate it but yeah.. Thanks.
+Amol Joshi hey whatever works! Glad you figured it out! Good job
Great post
Thanks Pratik!
good , can you help me how to compare the value in tow table in power bi, I want to create new cal column in table 1 with name "message "to store value Equality if the ID# in table 1 equal ID# in table 2, also if ID# in table 1 equal ID# in table 3
Hi, this fits exactly what I am looking for! I am now just running into the issue where the Matched Key is duplicating keys (DogQuick). In the video you just say to adjust the code to account for this. I am new to DAX and can't figure out how to adjust. Can you help out?
Glad you liked it! I think in this case you can use the LOOKUPVALUE function instead of the CONCATENATEX and FIND combination. This will return the first instance of a match. Let me know if this helps push you in the right direction!
HI I have a same problem, Have you found solution?
Hi, I just found a good solution to this
matched key =LOOKUPVALUE(keywords[Result],keywords[Keywords],FIRSTNONBLANK(FILTER(VALUES(keywords[Keywords]),SEARCH(keywords[Keywords],Data[Data],1,0)),1))
@@tchaitow this works perfect, thanks for posting
Killer video - thanks!!
Thanks Jeremiah!
Hi, great video! Is there a way to isolate exact matches by word? Example I'm looking for only att and want to ignore watt. Any suggestions on what changes i can make to the code? thank you.
Hey Sammer, use the FIND function instead of SEARCH. Find looks for exact matches while SEARCH looks for if the word contains the substring.
Sweet! Thank you. Another way I've been using is to put space before amd after the exact word. Cheers
@@sammertalib8898 That's a clever idea! Thanks for sharing
Hmmmm, my code's the exact same but my search (and FIND, when I tried that instead) is returning every possible value for every true result returned by the IF statement - i.e. They're all "Not Found" or "DogQuick". Any suggestions?
Great video! Thanks!
No problem! Thanks for watching the channel
How do we link tables with 2 names vs 4 names
Hey Rajan, could you give a little more detail about what you're trying to accomplish?
How do I add a space dynamically to the search terms? I have a huge list of search terms and want to add a space on the end. I tried concatenate but could get it to work
great post !
what about looking up a value ( % ) with different criteria
example:
cumulative sales for customer 1 = between 100.000 and 200.000 then discount 2% on
cumulative sales for customer 2 = between 100.000 and 200.000 then discount 1%
i have a fac table and a description of the problem
thanks in advance
Roger
Will this work if I have Composite (mixed) mode. I have SearchKey is an Imported data and To be searched in a Table with Direct Query?
Hi This is amazing I just need if the return value is unique. For xample I only want Dog and not DogQuick
Hey, how would I go about only returning the first found value?
Hey George, I would use the LOOKUPVALUE function. It's easier to use than the method in this video
where can i find the lookupvalue video
Can you do this in Excel with power queries or does it have to be in Power Bi?
Love it!!!!!!!!!!! Double like
Great to hear!
its also finding in between the words. But i need the whole word not in the middle
I have tried this logic, but for eg: in the text column if we find both values in one cell this logic won't work like (quick and dog) in one cell, please advise.
Would you please advise.
why this formula not working in excel power pivot?
Hi, this not working with excel power pivot?
when are u going to make the search text between two diff tables?
+Ahmed Ben Taher there are some times that you may need to! I made this video in response to a question from a subscriber where he needed to find all occurrences of a key from one table by searching another
Truly Help. But how to put a space between two string when concatenating? For instance, xplorer.exeexcel.exewinword.exe.
It has to be xplorer.exe, excel.exe, winword.exe
Hi Uma, you can add a " " at the end of the concatenatex formula.
Hi, could you please check the question on this link "community.powerbi.com/t5/Desktop/Fill-blanks-with-previous-value/td-p/492501" and advise as the solution provided doesn't seems to
work with me.
Man, I was looking for this info for days!!! Great vid! Like and sub from me
Nice! Glad you found the channel. Let me know if you ever have any ideas for future videos!