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

     

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