topic Re: Lookupvalue for a Search String in Tabular Model in DAX Commands and Tips

Lookupvalue for a Search String in Tabular Model

Anonymous — Thu, 10 Jun 2021 08:19:26 GMT

Hi seeking assistance with Tabular Model DAX query. Sorry for posting in PBI forum. I will be mgrating this solution to existng PBI, but the roadmap for it is 6-9 month away.

I've a Lookup/Reference table which provides the Sport Items and Sports related to it. Like

SPORT_ITEM SPORT _____________________________ BaseballBAT Baseball BASEBALL BAT Baseball Baseball Glove Baseball Helmet Football Shoulderpads Football Shoulder Pads Football

Then I have a table which has descriptive column. Like

ITEM_DESCRIPTION __________________ Baseballbat Needed Baseball Bat required Helmet wanted ShoulderPads provided Shoulder Pads needed

What I've been asked to do is - Lookup the value under ITEM_DESC and to the string matching SPORT_ITEM and return SPORT name column.

So I should see

ITEM_DESCRIPTION SPORT __________________________________ Baseballbat Needed Baseball Baseball Bat required Baseball Helmet wanted Football ShoulderPads provided Football Shoulder Pads needed Football

Note:

Unfortunately there is no relationship between the 2 tables. This lookup is the only way to join.
I cannot do this join in the data source because I'm not allowed by the DBAs. Long story, (you didnt hear me say red tape).
I have tried LOOKUPVALUE. Though, it returns expected value only when there is exact match to search string.
I need to match on Substring.
I tried using SEARCH along with LOOKUPVALUE, in 3rd argument. But I cannot because for SEARCH the lookup table is out of scope. it will only read from the table it is used in.

Since there is no join, I cannot use Related, right? And since this is Tabular Model, there is no CONTAINSVALUE.

Please let me know how this can be achieved.

Any help is much appreciated

Re: Lookupvalue for a Search String in Tabular Model

Tanushree_Kapse — Thu, 10 Jun 2021 08:35:11 GMT

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.

Re: Lookupvalue for a Search String in Tabular Model

Anonymous — Thu, 10 Jun 2021 08:43:59 GMT

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.

Re: Lookupvalue for a Search String in Tabular Model

Tanushree_Kapse — Thu, 10 Jun 2021 08:43:30 GMT

Anonymous , that's why we are CAPITALIZING every letter and splitting the column to get the substring seperated.

Re: Lookupvalue for a Search String in Tabular Model

Anonymous — Thu, 10 Jun 2021 08:45:46 GMT

But splitting cannot be 100%. i.e. I can have spaces in between for the text I need to search.

base ball

Shoulder pad

Re: Lookupvalue for a Search String in Tabular Model

Tanushree_Kapse — Thu, 10 Jun 2021 09:08:59 GMT

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!

Re: Lookupvalue for a Search String in Tabular Model

Anonymous — Thu, 10 Jun 2021 09:56:11 GMT

Thanks Tanushree_Kapse - I'll come back to this later. A priority 2 incident has happened. 😞

Any other thoughts in meanwhile are welcome.

Re: Lookupvalue for a Search String in Tabular Model

Anonymous — Fri, 11 Jun 2021 21:43:03 GMT

Tanushree_Kapse Hi I'm back on this.

Sorry I forgot to specify that it is not necessary that ITEM_DESCRIPTION will contain 1 part at the end that can be discarded. It can be like

Baseball bat wanted by Team AZ BaseballBat needed TeamCA Base ball Team NY Helmet

It can be any weird combination.

Hence I am specifcally looking for a lookup on part of string.

Sorry for not being clear.

Any help is much appreciated.

Re: Lookupvalue for a Search String in Tabular Model

Anonymous — Mon, 14 Jun 2021 04:58:23 GMT

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.

Solution Link