SUMMARIZECOLUMNS from two tables where column B contains a string matching the text of column A

[kstiegler]

I have two columns in separate tables, Accounts[Services] and Services[Name], and I'm trying to create a new table where one column is a list of values from Accounts[Services] and the other column is the matching value from the Services[Name] column.  Both columns will have repeated values, but there should be only unique combinations when considering both columns. Example:

Services

Name

Car wash

Air tires

Change oil

Account

Services

Car wash|Air tires|Change oil

Car wash|Change oil

Change oil

Intended result

Services[Name]	Account[Services]
Car wash	Car wash|Air tires|Change oil
Car wash	Car wash|Change oil
Air tires	Car wash|Air tires|Change oil
Change oil	Car wash|Air tires|Change oil
Change oil	Car wash|Change oil
Change oil	Change oil

In the above table, rows from both Services[Name] and Account[Services] are duplicated, but there are only unique combinations when considering both column in any given row.

Using SUMMARIZECOLUMNS with both columns includes all results from both columns. I haven't been able to figure out how to correctly filter the results.

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26RiWK1tVd3F5r5-hoP?e=7uhC2X

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

[kstiegler]

This is exactly what I needed, thank you!

I actually ended up solving it myself in Query Editor after it was suggested by another poster, but I may go back and use this DAX solution now.

[Ashish_Mathur]

Hi,

This should ideally be done in the Query Editor (not in DAX).  Would you be OK with that approach?

[kstiegler]

Yes, Query Editor could work. I was looking to do it in DAX because my Account[Services] column is currently a calculated column rather than a column defined in Query Editor.

[Ashish_Mathur]

Contradiction in your previous statement.  Don't know what approach you want to take.