Forum Discussion
Helper IVTable Relationship by ID and Date Range
Hi,
I want to create one to many relationships from Table 1 to Table 2. But I can’t do that since the primary key column in table 1 is not one column. The employee ID and Approval Start and Approval Finish is unique. Basically, I want in the reports based on employee ID and transaction date in table 2, power Bi finds the position of that employee from table 1 if the transaction date is between approval start and finish date. Always there will be one record with ongoing in the table 1 for employees if the previous position has not been ended.
Table 1 | Employee ID | Approval Start | Approval Finish | Position |
100 | 1-Jan-20 | 20-Jul-20 | Senior | |
100 | 21-Jul-20 | Ongoing | Principal | |
200 | 1-Jan-20 | 20-Sep-20 | Lead | |
200 | 21-Sep-20 | Ongoing | Manager | |
400 | 1-Jan-20 | Ongoing | Senior |
Table 2 | Employee ID | Transaction Date | Position( based on Id/date in table 1) |
100 | 1-Feb-20 | Senior | |
100 | 1-Mar-20 | Senior | |
100 | 1-Aug-20 | Principal | |
200 | 1-Aug-20 | Lead | |
200 | 1-Oct-20 | Manager | |
200 | 10-Oct-20 | Manager | |
400 | 1-Feb-20 | Senior | |
400 | 1-Mar-20 | Senior |
Thanks,
Amir
3 Replies
- mahoneypat
Microsoft Employee
With no relationship between the two tables, this column expression will find the position at that time.
Position =
VAR thisdate = Table2[Transaction Date]
VAR thisID = Table2[Employee ID]
RETURN
CALCULATE (
MIN ( Table1[Position] ),
Table1[Employee ID] = thisID,
Table1[Approval Start] <= thisdate,
Table1[Approval Finish] >= thisdate
)Regards,
Pat
amirghaderiThanks for the response.
The forluma does not work since there are Ongoing values as well in the date columns in table 2. I can get around that with converting all ongoing to be blank.
But, there are other relationships between Table 1 and other tables wihich allows me to use other tables grouping as well in the reporting. So, I need that relationship, if possible. Otherwise, I should bring all those othetr tables data as column to table 2 and table 2 already has huge data and will becomes very slow if I try to add many calculated columns.
HI amirghaderi,
I'd like to suggest you take a look at the following blog if your table relationships are based on multiple fields:
Relationship in Power BI with Multiple Columns
Regards,Xiaoxin Sheng
