Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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"
Solved! Go to 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)
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)
Or not...:
a simple solution is to create a duplicate Dim_Table and link them
Thank you kindly! I will try that.
This is what I was looking for, thank you very much for your time and help. Really appreciate it!!
plse see file, Is this what you are looking for?
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
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?
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
70 | |
38 | |
28 | |
26 |
User | Count |
---|---|
97 | |
88 | |
59 | |
43 | |
40 |