Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
coleent
New Member

Joining table on Date with another table and two Dates?

Hi, is it possible to create a relationship between two tables with one date column in the first table and two date columns in the second table? Or would it be better to just write it into the query when loading the data?

 

Example:

 

Table: TimeDetail
Column: Date

 

Table: PayPeriodSchedule
Column: PayPeriodStartDate
Column: PayPeriodEndDate

 

Would like to join TimeDetail table to PayPeriodSchedule table if Date is >= PayPeriodStartDate and <= PayPeriodEndDate.

 

I originally wrote the join in my query (resulting in a single table) but I want to know if it can be kept as separate queries/tables in Power BI and the relationship created there.

 

Thank you.

 

1 ACCEPTED SOLUTION
DAX0110
Resolver V
Resolver V

You would need a row identifier column in PayPeriodSchedule : let's call it PayPeriodID

 

If I assume correctly that you want to identify the pay period  that a TimeDetail entry belongs to, you can add a calculated column to the TimeDetail table with this formula:

 

=VAR thisDate = TimeDetail[Date]
RETURN CALCULATE(
    FIRSTNONBLANK(PayPeriodSchedule[PayPeriodID],0)
    , FILTER( ALL(PayPeriodSchedule)
        , PayPeriodSchedule[PayPeriodStartDate] <= thisDate

       && thisDate <= PayPeriodSchedule[PayPeriodEndDate]
        )
    )

 

 

 

 

View solution in original post

1 REPLY 1
DAX0110
Resolver V
Resolver V

You would need a row identifier column in PayPeriodSchedule : let's call it PayPeriodID

 

If I assume correctly that you want to identify the pay period  that a TimeDetail entry belongs to, you can add a calculated column to the TimeDetail table with this formula:

 

=VAR thisDate = TimeDetail[Date]
RETURN CALCULATE(
    FIRSTNONBLANK(PayPeriodSchedule[PayPeriodID],0)
    , FILTER( ALL(PayPeriodSchedule)
        , PayPeriodSchedule[PayPeriodStartDate] <= thisDate

       && thisDate <= PayPeriodSchedule[PayPeriodEndDate]
        )
    )

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.