New Column based on match of 2 fields in same table

[mdslater]

I'm trying to do a match based on 2 criteria from within the same table.

e.g.

My table looks like: 

ID	employeeID	Affilliation	AffilliationType	titleFor
1	12	Internal Services	Division
2	5	Health Care Protection	Unit
3	22	Chief	Title	Unit
4	46	Consumer Services	Division
5	22	Education & Communications	Unit
6	19	Human Resources	Division

I want to add a column that looks for a match of the employeeID and AffilliationType based on employeeID and titleFor of the record.  The result would be:

ID	employeeID	Affilliation	AffilliationType	titleFor	titleAffilliation
1	12	Internal Services	Division
2	5	Health Care Protection	Unit
3	22	Chief	Title	Unit	Education & Communications
4	46	Consumer Services	Division
5	22	Education & Communications	Unit
6	19	Human Resources	Division

Thanks in advance for any help or general direction for this.

[d_gosbell · Accepted solution]

You could do this with the DAX LookupValue function

eg.

= LOOKUPVALUE(Table1[Affilliation], Table1[AffilliationType], Table1[titleFor], Table1[employeeID], Table1[employeeID])