Forum Discussion

BalazsNy's avatar
BalazsNy
Helper I
1 year ago
Solved

Create relation based on condition

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.

Need Help
Tips and Tricks
  • MFelix's avatar
    MFelix
    1 year ago

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

     

1 Reply

  • MFelix's avatar
    MFelix
    Super User
    1 year ago

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