Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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]
)
)
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]
)
)
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |