Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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

100100_201/01/202305/05/20231
100100_201/01/202313/02/20232
100100_201/01/2023 3
100100_213/02/202315/02/20234
100100_213/02/2023 5
100100_113/12/202222/01/20236

 

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"

 

1 ACCEPTED SOLUTION
Ahmedx
Super User
Super User

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)

Screenshot_1.png

View solution in original post

9 REPLIES 9
Ahmedx
Super User
Super User

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)

Screenshot_1.png

Anonymous
Not applicable

Or not...:

Nighthawk_1_0-1695217276248.png

 

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

DUMP Dim_Table = 'Dim_Table'
Anonymous
Not applicable

Thank you kindly! I will try that.

Anonymous
Not applicable

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

Ahmedx
Super User
Super User

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

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

Anonymous
Not applicable

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. 

You not need to create a new table, look

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

Anonymous
Not applicable

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?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.