Lookup between two tables, based on two dates and active relationship on NewID column (example incl)

Hello everyone,

I have a rather simple problem, but I can't get my head around it.

I have a fact table where I need to get:

if the "End Date" is set: 

Based on the "Start Date", the "End Date" and the "ID" a lookup on "Dim_Table" to check whether the dates fall between the given records that match the "ID" and then return the corresponding "New ID"

if the "End Date" is not set:

Lookup the "ID" on "Dim_Table" and check for the first blank "End Date" date and then return the corresponding "New ID".

The result should look like this and have an active relationship between the "New ID" column between the two tables.

ID  New ID   Start Date     End Date     Index

Thank you for any tips to solve this!

As I can't attach the file here and I can't use an external sharing site (business environment), please find the Power Query below, pardon for the inconvenience:

Fact_Table

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRAiIDQ30gMjIwMgZxTPWBCMyJ1cGlyNBY38CIkCJUKSQtQI4pdv0oijCkDMFSRkCOkRHcnthYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"New ID" = _t, #"Start Date" = _t, #"End Date" = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "ID", "New ID", "Start Date", "End Date"}),
#"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Start Date", type date}, {"End Date", type date}})
in
#"Changed Type"

Dim_Table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNIBkfGGQNrAUB+IjAyMjIEcYwQnVgdZpRFUpRFMpVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, ID_2 = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}})
in
    #"Changed Type"