Forum Discussion
Common date field
Hii RiDwiv14
You have four tables (TICKET, TRANSACTIONS, RESERVATION, EVENTS) each containing multiple date columns (Created At, Updated At, Check-in, etc.). You need a single Slicer on your dashboard that controls all these dates simultaneously without merging the tables into a single messy file.
The Solution: The "Star Schema" Date Dimension
Instead of merging tables, you must create a central Date Table and connect it to every date field in your SQL tables using a mix of Active and Inactive relationships.
Step 1: Create a Central Date Table
In Power BI Desktop, go to the Modeling tab, select New Table, and use this DAX:
DateTable = CALENDAR( MIN('TICKET'[created at]), MAX('TRANSACTIONS'[updated at]) )Citations:
Step 2: Establish Relationships
Go to the Model View and create the following connections:
- Connect DateTable[Date] to TICKET[created at] (Active).
- Connect DateTable[Date] to TICKET[updated at] (Inactive).
- Repeat this for TRANSACTIONS, RESERVATION, and EVENTS. You will have one solid line (Active) and several dotted lines (Inactive) for each table.
Step 3: Create Measures to "Activate" the Dates
Since only one relationship can be active at a time, use the USERELATIONSHIP function in your measures to tell Power BI which date to use when you select a filter.
Example Measure for Updated Tickets:
Tickets Updated Count = CALCULATE( COUNT('TICKET'[TicketID]), USERELATIONSHIP('DateTable'[Date], 'TICKET'[updated at]) )Citations:
Why this is the best fix:
- Performance: Your SQL tables remain lean and fast because you aren't duplicating rows.
- One Slicer to Rule Them All: When you put DateTable[Date] into a Slicer, it will filter your "Active" dates by default and your "Inactive" dates via the measures created in Step 3.
- Flexibility: You can now compare "Tickets Created" vs "Tickets Updated" on the exact same chart using a single X-axis.
Summary for the Community
Do not merge your SQL tables into one. Use a Central Date Table (Canonical Date) and the USERELATIONSHIP DAX function. This is the only way to maintain a scalable model in Power BI.
If this Star Schema approach resolves your multi-table date issue, please mark this as the "Accepted Solution"!
Hi RiDwiv14
Create One Date (Calendar) table
Create a master Date table that covers all dates across all tables.
Date =
ADDCOLUMNS (
CALENDAR (DATE(2025,1,1), DATE(2030,12,31)),
"Year", YEAR([Date]),
"Month", FORMAT([Date], "MMM"),
"MonthNo", MONTH([Date]),
"DateKey", FORMAT([Date], "YYYYMMDD")
)
Mark this as Date table.
Decide how users should filter by date
Since each table has multiple date columns, you have a valid pattern i.e., Role-Playing date relationships
Use one Date table and connect it to multiple date fields (inactive relationships).
Example relationships
Date → Ticket[CreatedAt] (Active)
Date → Ticket[UpdatedAt] (Inactive)
Date → Transactions[TransactionDate] (Active)
Date → Reservation[CheckInDate] (Inactive)
Date → Reservation[CheckOutDate] (Inactive)
Date → Events[EventDate] (Active)
Date → PermitRequest[CreatedAt] (Active)
Then use USERELATIONSHIP() in measures.
Example:
Tickets by Created Date =
CALCULATE(
COUNT(TICKET[TicketID]),
USERELATIONSHIP(Date[Date], TICKET[CreatedAt])
)
Please mark it as a solution with headup if this helps you. Thank You!
hi, i got the same answer from chatgpt but it is not working because each table itself has 3-4 date field
