Forum Discussion
Common date field
I have fetched four tables from my SQL database named inventory TO power bi desktop. The four tables are TICKET, TRANSACTIONS, RESERVATION, EVENTS AND PERMIT REQUEST. All the four table have multiple date field more than one. Example created at ,updated at , check in time , check out time. first combine all the table into one and create one date field which will be connected to all the date field. and when we will select that date field on the dashboard of power bi it will give the result of all the connected date field. or create one date that will be the combination of all the dates. So that whenever that datefield will be selected any transaction or ticket details will be showed
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"!
8 Replies
Please read about inactive relationships, and how to use USERELATIONSHIP to temporarily activate them in your DAX measures.
lbendlin , I agree with your suggestion. Thanks for the response.
RiDwiv14 , Some more references and related concepts:
- USERELATIONSHIP - DAX Guide
- How to Handle Multiple Date Columns in Power BI
- Comparing TREATAS vs USERELATIONSHIP in Power BI
Hope this helps.
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"!
AshokKunwarthank you 😊 While it is working fine on my end, I will consult with my senior regarding any additional requirements.
Your welcome 🤗,and thank you for mark this solution as the Accepted Solution, it helps close the thread and assists others searching for the same fix."
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
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
