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 ,
Thanks for reaching out to the Microsoft fabric community forum.
When each table has multiple date columns, a single date slicer can’t automatically filter all of them. Simply creating one Date table won’t solve it, because Power BI only allows one active date relationship per table and the slicer won’t switch between different date fields on its own.
To make this work, you either need to define your measures so they explicitly use the correct date column, using inactive relationships and USERELATIONSHIP, which is the recommended approach, or reshape the data by unpivoting all date columns into a single date events table if you want the slicer to catch any activity on a given date. Another option is to let users choose which date they want to analyze and handle that logic in DAX. There isn’t a way to have one slicer control multiple date columns automatically without using DAX or changing the model.
Best Regards,
Tejaswi.
Community Support