Relationship through multiple criteria

DAX DAX Calculations
1

So I have two tables. One is a reference table and the other is the actual data table. What I need to do is return a value from the reference table into the data table based on a number of criteria matching between the tables.

Fields in Reference Table
Instructor Type
Location Type
Career Type
Minimum Enrollment
Maximum Enrollment
Pay Rate

Data Table
Instructor Type
Location Type
Career Type
Total Enrollment

So I want to return the pay rate based on Instructor Type in the data table matching instructor Type in the reference table and Location Type matching and Career Type matching then if the Total Enrollment falls between the Min and Max.

I am having trouble jumping through so many criteria needing to match.

2

Hi,

You could create a measure using a virtual table with SUMMARIZE or prepare your data in Power Query, here you can merge based on multiple criteria in multiple columns, to show this I have enclosed an example in Excel with PowerQuery and create your measures based on this.

Multiple relationships.xlsx (216.0 KB)

Paul

Enterprise%20DNA%20Expert%20-%20Small