cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Create a relationship between tables similar to SQL LIKE clause

Hi dear community experts, 

 

hope my message finds you well.

 

The issue I am facing now is the following:

I have 2 tables and each table contains promotion code column. I am planning to set up a relationship between those 2 tables based on that column, which is the only option. 

 

My problem is the values from code column (Table1) and code column(Table 2) are not identical. In Table 2 some additionl letters are generated and added to inital base code. 

For example the value family20 from Table1 refers to the following list of values in Table 2:

  1. family20abc
  2. family20def
  3. family20tfr
  4. family20poi
  5. family20bhn

In SQL this could be done using LIKE operator in WHERE clause. However, the modelling is done in Power BI, so alternative solution needs to be applies. 

I have checked NATURALLEFTOUTERJOIN Dax function, however I do not see as it can work. 

 

Will be very grateful for any advice/tip you can share on this. 

 

Many thanks in advance and nice weekend to all. 

 

Cheers, 

Saro

 

 

 

 

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

hi  @Anonymous 

For your case, you could try this simple way as below:

Step1:

Create a new code column in table 2 as below:

New code = FIRSTNONBLANK(FILTER(VALUES('Table 1'[code]),SEARCH('Table 1'[code],'Table 2'[code],1,0)),1)

Step2:

Then use this new code to create a relationship with table1.

1.JPG

 

here is simple sample pbix file, please try it.

 

Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
v-lili6-msft
Community Support
Community Support

hi  @Anonymous 

For your case, you could try this simple way as below:

Step1:

Create a new code column in table 2 as below:

New code = FIRSTNONBLANK(FILTER(VALUES('Table 1'[code]),SEARCH('Table 1'[code],'Table 2'[code],1,0)),1)

Step2:

Then use this new code to create a relationship with table1.

1.JPG

 

here is simple sample pbix file, please try it.

 

Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

Dear

@v-lili6-msft  thanks for magic. Really appreciate it!

 

Sincerely, 

Saro

 

@v-lili6-msft 

 

Nice Idea 🙂

 

It reminds me of how I used play with strings in excel using LEFT, RIGHT, MID, SEARCH, etc...  Usually these kind of ideas comes to people who are really good in excel formulas and then learn DAX than people who learns DAX first. 

 

I liked it. 🙂

parry2k
Super User
Super User

@Anonymous why not in Power Query create another column and drop the extra letters from the code column so that you can create relationship between these two tables.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Anonymous
Not applicable

Hi @parry2k 

 

thanks for reply. There is not strict rule or delimeter I can use for that purpose. 

The initial/base codes from Table 1 have different lenght, so respectively different are the lenghts od codes from Tables 2 where additional piece is added. 

 

Any recommendation with this limitation being relevant?

 

Thanks again!

@Anonymous maybe use fuzzy match in power query and get code from another table, although if you can solve it at the source, it will be the best solution.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Anonymous
Not applicable

Thanks @parry2k 

Anonymous
Not applicable

Dear  @Fowmy , @amitchandak 

 

will be very grateful, if you could share your opinion on this. 

 

Thanks in advance.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.