how to make table show data not summarized by date

[Anonymous]

Hello
When I try to show the non-summarized field in the table power bi cannot identify the relationship between the tables.
I attach my file to make it easier for everyone.

I apologize for my English
please help me!

I want this:

but I get this:

pbix file 

Thank you very much in advance

[Anonymous]

Thank you very much in advance

[vik0810 · Accepted solution]

Hi @Anonymous,

convert the "id employee" column of your department table to integer.

Create a calculated column "current department" in absence table with following definition

current department = 
var IdEmployee = [id employee]
var DateOfAbsence = [date of absence]
var DepartmentDate = CALCULATE(MAX(Department[start date]), Department[start date] <= DateOfAbsence)
Return CALCULATE(VALUES(Department[Department]), Department[id employee] = IdEmployee, Department[start date] = DepartmentDate)

Use this column in your visualization instead of department.

[Anonymous]

@vik0810 thank you very much. works perfectly