In Power Pivot it will help to know how to run a DAX function that is the equivalent of the commonly employed VLOOKUP formula in Excel. The DAX LOOKUPVALUE function is particularly useful because it allows you to enter two criteria to search against the table from which you want to pull data. So the basic format is:
= LOOKUPVALUE( Table[OutputColumn], Table[LookupColumn1], “SearchValue1”, Table[LookupColumn1], “SearchValue2”)
'Table[OutputColumn]' contains the value from the table you're pulling data from. 'Table[LookupColumn1]' is the first column from that Table that you're looking for a match from. 'SearchValue1' contains an entry from the table you're working in that is adjacent to the present cell. The second 'Table[LookupColumn1]' is the second column from that Table that you're looking for a match from, to another cell in table you're working in (adjacent to the other cell from which something in the first 'Table[LookupColumn1] is found) that is defined as 'SearchValue2'.
So in my Consumer_Complaints table imported into Power Pivot there are certain sub-products for which we are focusing on particular issues that have been designated Code Red and Code Blue.
[the two tables appear as separate worksheets in Power Pivot]. This screen grab above shows the table that we're pulling data from. When there are adjacent cells from the Sub-product and Issue columns from the 'Consumer_Complaints' table that are either both, 'Other mortgage' and 'Settlement process and costs', OR 'Auto' and 'Communication tactics', you get a hit. The function that is entered in the formula box for a new column to contain the returned data is:
= LOOKUPVALUE( Book1[F3], Book1[F1], Consumer_Complaints[Sub-product], Book1[F2],Consumer_Complaints[Issue])
It will autofill for all rows in the Power Pivot table as soon as it is entered, and you get the results in 'Calculated Column 1'. When entering the formula, you'll benefit from Power Pivot's ability to automatically find the fields as you type them in.
See this demonstration video: