Join table with composed date and string keys

[spizzo]

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

[Greg_Deckler · Accepted solution]

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")

[Eric_Zhang · Accepted solution]

@spizzo

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]
)

[spizzo]

@Eric_Zhang : thank you Eric, i used your solution and every thing works like i need...

[Greg_Deckler]

Create a column in each table that combines the fields. Then, use those columns to relate your tables.

[spizzo]

@Greg_Deckler

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

[Greg_Deckler · Accepted solution]

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")

[spizzo]

@Greg_Deckler thank you