Forum Discussion
Solved
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, employe...
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] )
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")