Forum Discussion
Create a relationship between date ranges in different tables with a unique date in a Calendar Table
Hello everyone,
I have im my semantic model tables like these ones. I want to know how to create a relationship between the various data ranges columns(Start_Date, End_Date) present in Tables 1, 2 and 3 and the column Date in the Calendar Table?
My approach was to create in Powerquery in Tables 1, 2 and 3 this column(DateRange)
#"Added Date Range" = Table.AddColumn(#"Filtered Rows2", "DateRange", each
if [Start_Date] <= [End_Date] then
List.Dates([Start_Date], Duration.Days([End_Date] - [Start_Date]) + 1, #duration(1, 0, 0, 0))
else
{}), // Return empty list if the date range is invalid
It works, but the issue is that it gets too heavy to load my model.
Is there a better approach?
Thank you very much
Hi elcamino,
Instead of creating columns in each table in Power Query you can create a calendar table using DAX, try below DAX
DateBridge =
VAR MinDate = MINX( UNION(SELECTCOLUMNS(Table1, "Date", Table1[Start_Date]),
SELECTCOLUMNS(Table1, "Date", Table1[End_Date]),
SELECTCOLUMNS(Table2, "Date", Table2[Start_Date]),
SELECTCOLUMNS(Table2, "Date", Table2[End_Date]),
SELECTCOLUMNS(Table3, "Date", Table3[Start_Date]),
SELECTCOLUMNS(Calendar, "Date", Calendar[Date]) ), [Date])
VAR MaxDate = MAXX( UNION(SELECTCOLUMNS(Table1, "Date", Table1[Start_Date]),
SELECTCOLUMNS(Table1, "Date", Table1[End_Date]),
SELECTCOLUMNS(Table2, "Date", Table2[Start_Date]),
SELECTCOLUMNS(Table2, "Date", Table2[End_Date]),
SELECTCOLUMNS(Table3, "Date", Table3[Start_Date]),
SELECTCOLUMNS(Calendar, "Date", Calendar[Date]) ), [Date])
RETURN ADDCOLUMNS( CALENDAR(MinDate, MaxDate), "DateKey", [Date])Connect DateBridge[Date] to Calendar[Date] (Many-to-One).
Create relationships between DateBridge[Date] and Table1[Start_Date], Table2[Start_Date], etc.
If you only need to filter data dynamically, use a measure instead of expanding records.
IsDateInRange =
VAR CurrentDate = SELECTEDVALUE(Calendar[Date])
RETURN
IF(
MAX(Table1[Start_Date]) <= CurrentDate &&
MAX(Table1[End_Date]) >= CurrentDate, 1, 0
)Use this measure as a visual-level filter (=1 to show valid data)
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!
2 Replies
- grazitti_sapna
Super User
Hi elcamino,
Instead of creating columns in each table in Power Query you can create a calendar table using DAX, try below DAX
DateBridge =
VAR MinDate = MINX( UNION(SELECTCOLUMNS(Table1, "Date", Table1[Start_Date]),
SELECTCOLUMNS(Table1, "Date", Table1[End_Date]),
SELECTCOLUMNS(Table2, "Date", Table2[Start_Date]),
SELECTCOLUMNS(Table2, "Date", Table2[End_Date]),
SELECTCOLUMNS(Table3, "Date", Table3[Start_Date]),
SELECTCOLUMNS(Calendar, "Date", Calendar[Date]) ), [Date])
VAR MaxDate = MAXX( UNION(SELECTCOLUMNS(Table1, "Date", Table1[Start_Date]),
SELECTCOLUMNS(Table1, "Date", Table1[End_Date]),
SELECTCOLUMNS(Table2, "Date", Table2[Start_Date]),
SELECTCOLUMNS(Table2, "Date", Table2[End_Date]),
SELECTCOLUMNS(Table3, "Date", Table3[Start_Date]),
SELECTCOLUMNS(Calendar, "Date", Calendar[Date]) ), [Date])
RETURN ADDCOLUMNS( CALENDAR(MinDate, MaxDate), "DateKey", [Date])Connect DateBridge[Date] to Calendar[Date] (Many-to-One).
Create relationships between DateBridge[Date] and Table1[Start_Date], Table2[Start_Date], etc.
If you only need to filter data dynamically, use a measure instead of expanding records.
IsDateInRange =
VAR CurrentDate = SELECTEDVALUE(Calendar[Date])
RETURN
IF(
MAX(Table1[Start_Date]) <= CurrentDate &&
MAX(Table1[End_Date]) >= CurrentDate, 1, 0
)Use this measure as a visual-level filter (=1 to show valid data)
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!Thank you very much. It worked.
