Forum Discussion

RiDwiv14's avatar
RiDwiv14
New Member
7 months ago
Solved

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...
Need Help
  • AshokKunwar's avatar
    AshokKunwar
    7 months ago

    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:

    1. ​Connect DateTable[Date] to TICKET[created at] (Active).
    2. ​Connect DateTable[Date] to TICKET[updated at] (Inactive).
    3. ​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"!