Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Lookupvalue for a Search String in Tabular Model

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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 

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

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 

Tanushree_Kapse
Impactful Individual
Impactful Individual

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.

Anonymous
Not applicable

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.

Anonymous
Not applicable

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!

Anonymous
Not applicable

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

 

Any other thoughts in meanwhile are welcome.

Anonymous
Not applicable

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors