Forum Discussion
Join table with composed date and string keys
Hi Folks,
i'm a PowerBI's newbie , so i think you'll be able to help me in my first steps.
I have two tables, one for employees list of my company, the other for attendance events .
So, employees list has a specific PK but isn't a FK of attendance events.
THE TWO Tables are related by this join condition:
EVENTS.IDEMPLOY = EMPLOYEES.IDEMPLOY AND ( EVENTS.EVENT_DATE BETWEEN EMPLOYEES.INITIAL_DATE AND EMPLOYEES.FINAL_DATE )
I try to use Manage Relationships to make a similar join, but POWERBI dosn't like multiple records for join condition.
I try to make a merge table, but the result isn't i need.
There is anybody can help me?
Thank you all for your understanding and
for all your assistance
Simone
example files HERE
Right, the column in your two tables will be calculated differently.
In your Employee table, you would do something like:
JoinColumn = [EmployeeID] & "1"
In your Events column, you would have something like:
JoinColumn = IF([Date]>RELATED(Employees[StartDate]),IF([Date]<RELATED(Employees[EndDate],[Employee] & "1",[Employee] & "0"),[Employee] & "0")
I'd prefer a merge table.
Merge Table = SELECTCOLUMNS ( FILTER ( CROSSJOIN ( EMPLOYEE, EVENTS ), EMPLOYEE[IDEMPLOY] = EVENTS[IDEMPLOY] && EMPLOYEE[INIT_DATE] <= EVENTS[DATE_EVENT] && EVENTS[DATE_EVENT] <= EMPLOYEE[FINAL_DATE] ), "IDEMPLOY", EMPLOYEE[IDEMPLOY], "CATEGORY", EMPLOYEE[CATEGORY], "FINAL_DATE", EMPLOYEE[FINAL_DATE], "FLNUMROW", EMPLOYEE[FLNUMROW], "IDCOMPANY", EMPLOYEE[IDCOMPANY], "IDEMPLOY_ALT_KEY", EMPLOYEE[IDEMPLOY_ALT_KEY], "INIT_DATE", EMPLOYEE[INIT_DATE], "DATE_EVENT", EVENTS[DATE_EVENT], "IDEVENT", EVENTS[IDEVENT], "QTA_EVENT", EVENTS[QTA_EVENT] )
6 Replies
I'd prefer a merge table.
Merge Table = SELECTCOLUMNS ( FILTER ( CROSSJOIN ( EMPLOYEE, EVENTS ), EMPLOYEE[IDEMPLOY] = EVENTS[IDEMPLOY] && EMPLOYEE[INIT_DATE] <= EVENTS[DATE_EVENT] && EVENTS[DATE_EVENT] <= EMPLOYEE[FINAL_DATE] ), "IDEMPLOY", EMPLOYEE[IDEMPLOY], "CATEGORY", EMPLOYEE[CATEGORY], "FINAL_DATE", EMPLOYEE[FINAL_DATE], "FLNUMROW", EMPLOYEE[FLNUMROW], "IDCOMPANY", EMPLOYEE[IDCOMPANY], "IDEMPLOY_ALT_KEY", EMPLOYEE[IDEMPLOY_ALT_KEY], "INIT_DATE", EMPLOYEE[INIT_DATE], "DATE_EVENT", EVENTS[DATE_EVENT], "IDEVENT", EVENTS[IDEVENT], "QTA_EVENT", EVENTS[QTA_EVENT] )Eric_Zhang : thank you Eric, i used your solution and every thing works like i need...
Create a column in each table that combines the fields. Then, use those columns to relate your tables.
Ok, i supposed to do it, but there are a variety of dates in event table and thera are only two dates in employees tables.
I which kind i can relate those new columns?
Thanks & Regards
Right, the column in your two tables will be calculated differently.
In your Employee table, you would do something like:
JoinColumn = [EmployeeID] & "1"
In your Events column, you would have something like:
JoinColumn = IF([Date]>RELATED(Employees[StartDate]),IF([Date]<RELATED(Employees[EndDate],[Employee] & "1",[Employee] & "0"),[Employee] & "0")
