How to look up a value based on the result between min and max in other table

[YGL18]

StudentAssessmentScoreResult table:

LearningGains Table:

Hello,

How can I look up LearningGainLevel in Table_2 (which needs to go in the LearningGainLevel of Table_1 ) so that Result from Table_1 is between Min and Max of Table_2.

Also, two tables need to be joined on AssessmentTitle and GradeLevel to get the right LerningGainLevel from Table_2 to fill out LearningGainLevel of Table_1.

Please help,

Thank you!

[Anonymous · Accepted solution]

LearningGainLevel = -- (column)
var __result = StudentAssessmentScoreResult[Result]
var __assessmentTitle = StudentAssessmentScoreResult[AssessmentTitle]
var __gradeLevel = StudentAssessmentScoreResult[GradeLevel]
var __gainLevel =
	MAXX(
		filter(
			LearningGains,
			LearningGains[Min] <= __result
			&& __result <= LearningGains[Max]  
			&& LearningGains[SubLevel] <> "Overall"
			&& LearningGains[AssesmentTitle] = __assessmentTitle
			&& LearningGains[GradeLevel] = __gradeLevel 
		),
		LearningGains[LearningGain],
	)
return
	__gainLevel

This works regardless of relationships between the tables.

By the way, you CANNOT create a relationship between two tables in Power BI based on 2+ fields. There must be 1 field to join on in each table to be able to do it.

Best

Darek

[YGL18]

Thank you!!!