Create relation based on condition

[BalazsNy]

Dear All,

I have 2 tables, which I like to connect to each other.

First is a manager list table, including manager name / department / start & end date of belonging to that certain department.

2nd table is for feedbacks, with the date (of submission) / Manager name / rate / comment / category & employee ID.



I want to create a report with a slicer to be able to filter feedbacks by Departments. Therefore, I need to create a relationship between these tables via Manager name column, but it should also check when the feedback is submitted because a manager can be relocated between the departments by the time (see start / end date).

Do you have any idea, how to solve this issue & how relationship between tables should look like.

Thank you in advance.

[MFelix · Accepted solution]

Hi @BalazsNy ,

Do you want to have a visualization or a column on your model?

You can use the following code for a column on the feedback table:

	MAXX(
		CROSSJOIN(
			FILTER(
				Managers,
				Managers[Start Date] <= 'Feedback list'[Date] && Managers[End Date] >= 'Feedback list'[Date] && Managers[Manager Name] = 'Feedback list'[Manager]
			),
			'Feedback list'
		),
		Managers[Department]
	)

If you want a measure just try the following code:

Department= 
        MAXX(
			CROSSJOIN(
				FILTER(
					Managers,
					Managers[Start Date] <= MAX('Feedback list'[Date]) && Managers[End Date] >= MAX('Feedback list'[Date]) && Managers[Manager Name] = MAX('Feedback list'[Manager])
				),
				'Feedback list'
			),
			Managers[Department]
		)