Table Relationship by ID and Date Range

[amirghaderi]

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

[v-shex-msft]

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

[mahoneypat]

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

[amirghaderi]

Thanks 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.