Forum Discussion
Lookupvalue for a Search String in Tabular Model
For those who may stumble upon similar issue -
After a lot of reading everywhere, found the below link and especially in comments.
A combination of a various solutions in comments helped.
Hi Anonymous ,
First of all, you would need the same strings (as Power BI is case sensitive) in both the columns. For this, you can go to the Query editor-> select SPORT_ITEM column from table 1 and UPPERCASE every letter.
Similarly, first go to the ITEM_DESCRIPTION column of second table and split it by a delimiter " " - Rightmost (space).
You would get a new column with just the SPORT item names, Rename that to ITEMS.
Now UPPERCASE this column.
Close and Apply the changes.
Now, use your LOOKUPVALUE DAX on these columns:
new column in second table:
SPORT= LOOKUPVALUE( table_1(SPORT), table_1( SPORT_ITEM), table_2(ITEMS))
This would create the new column with SPORTS name in table 2.
I hope this helps!
If I answer your question, mark it as a solution.
Thanks a lot Tanushree_Kapse for replying.
This doesnot work. Like I mentioned, I tried this, but it matches only where the match is EXACT. However I'm looking for matching a substring of the column ITEM_DESCRIPTION.
The ITEM_DESCRIPTION column can have other text in there. Like
Baseball needed
Baseball
Helmet wanted
Also this is Tabular Model. Though solution should be via DAX.
Anonymous , that's why we are CAPITALIZING every letter and splitting the column to get the substring seperated.
But splitting cannot be 100%. i.e. I can have spaces in between for the text I need to search.
base ball
Shoulder pad
Anonymous , In that case: Use UPPER(Column_name) to convert every alphabet to uppercase.
And for splitting the column using delimiter for rightmost space-
RIGHT([ITEMS_DESCRIPTION],SEARCH(" ",[ITEMS_DESCRIPTION]),-1)I hope this helps!