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

[Anonymous]

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

100	100_2	01/01/2023	05/05/2023	1
100	100_2	01/01/2023	13/02/2023	2
100	100_2	01/01/2023		3
100	100_2	13/02/2023	15/02/2023	4
100	100_2	13/02/2023		5
100	100_1	13/12/2022	22/01/2023	6

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"

[Ahmedx · Accepted solution]

I'm not sure but try this

New ID = 
VAR  _step1 = MAXX(
    FILTER(
        ALL( Dim_Table ),
        Dim_Table[ID] = Fact_Table[ID]
            &&'Dim_Table'[End Date]>= Fact_Table[End Date]
    ),
    Dim_Table[ID_2]
)
VAR  _step2 = MAXX(
    FILTER(
        ALL( Dim_Table ),
        Dim_Table[ID] = Fact_Table[ID]
            &&ISBLANK('Dim_Table'[End Date])
    ),
    Dim_Table[ID_2]
)
RETURN
SWITCH( TRUE(),
     not ISBLANK(  _step1),_step1,
_step2)

[Anonymous]

Or not...:

[Ahmedx]

a simple solution is to create a duplicate  Dim_Table and link them

DUMP Dim_Table = 'Dim_Table'

[Anonymous]

Thank you kindly! I will try that.

[Anonymous]

This is what I was looking for, thank you very much for your time and help. Really appreciate it!!

[Ahmedx]

plse see file, Is this what you are looking for?

https://1drv.ms/u/s!AiUZ0Ws7G26Rimna-vkuq1yskSJd?e=7GosBt

[Anonymous]

Hi,

Almost. The "New ID" should be brought back to the "New ID" column in the "Fact Table". Meaning, not by creating a new table. 

[Ahmedx]

You not need to create a new table, look

https://1drv.ms/u/s!AiUZ0Ws7G26RimqndGXwlUyPe9y0?e=n5jov9

[Anonymous]

Thank you, I see.

My challenge is, that this example is simplified. The actual "Fact Table" is very large with ~1mio records and many (custom) columns with lookups etc. 

So I was wondering, isn't there a DAX solution to the problem?