Left Outer Join Unrelated Tables in Dax

[hhemphill]

I am trying to accomplish something similar to the sql statement below in Power BI. I have 2 tables that aren't joined in the model and I need to join on an Id column with effective date parameters. Can this be done in DAX?

Thanks

[vanessafvg]

@hhemphill 

What are you wanting to come out with?  can you give an example?

do you want to end up a with a table?  a measure?   that will drive different solutions.    Is this an imported model?  are you able to share some sample data with what you expecting to do with the data

[hhemphill]

I am wanting to have a variable under the sales table that has the product status from the product table. It needs to get the product record where the slider end date is between the Product Effective Date & the Product Expiration Date.

I do not want to directly join it due to the many to many relationship. I would like to create a Dax variable if possible.

In the example below I would expect Product Id 1 to have a status of Z & Product Id 2 to have a status of Z

The datasets are imported not direct query. Link to PBIX file PBI Example File

[vanessafvg · Accepted solution]

@hhemphill 

Hi

i changed your model back to the original data you provided

please look at this and see if it works for you