Relationship contains string (direct query)

[Will2020]

hi guys

apologies if this has been posted before, I have found a lot of references to this for non-DirectQuery but not many for DirectQuery that I can make work or understand.

I have two large SQL views as the data sources. I am trying to set the relationship between the two views in PowerBI desktop (Jan2020 version).

On one table there are quotes, and on the other there are opportunities. On the opportunity view there is a quote number and a revision (two separate columns).

On the quote view this is one column with a hyphen (quotenumber-revision). 

Further example below:

Opportunity

Quote_number	Revision
236892	3

Quote

Quote_number
236892-3

So I would like to get the relationship so that these two match, by dropping everything after the first hypen, or doing a "contains" somehow. The quote numbers are totally unique in the 'quotes' view, as this view only contains the newest quote revision. Therefore it still would maintain the one-to-many relationship.

It's direct query so I can't modify that data, the view is huge with millions of rows and it feeds from a production system. I just wonder if there is any way to do it in DAX or whether I could create another column which is not in the source DB.

thanks for the help in advance.

[sturlaws · Accepted solution]

Hi @Will2020,

have you tried to write a specific query to get the data from the Quote view? If you go to Power Query/Transform data, and on the Quote-table remove all steps but Source. Then doubble click on source, which will give you this popup:

Cheers,
Sturla

[amitchandak · Accepted solution]

@Will2020 , Try combinevalues, This is column supported for direct Query

https://docs.microsoft.com/en-us/dax/combinevalues-function-dax

https://www.sqlbi.com/articles/using-combinevalues-to-optimize-directquery-performance/

[Will2020]

Seems to work perfectly.

Thank you!

[sturlaws · Accepted solution]

Hi @Will2020,

have you tried to write a specific query to get the data from the Quote view? If you go to Power Query/Transform data, and on the Quote-table remove all steps but Source. Then doubble click on source, which will give you this popup:

Cheers,
Sturla