Please HELP!!! Looking for DAX function(s)/logic equivalent to an Excel formula: IF...INDEX...MATCH

[tranba]

I have an Excel file that I need to apply dynamic log to determine the parent child relationships for column B titled 'AU'.  The relationship is determines by the number of spaces before the AU name which is represented in column C titled 'AU'.  An index of zero is the level 1, 5 = level 2, 10 = level 3, and so on.  The lookup logic needs to find the first AU Index match and populate the cell(s) until the next matching value with the same AU index is found.  I am able to solve this problem in Excel, but I need to translate the logic in PowerBI Desktop.  Please help.  Any guidance would greatly be appreciated.

The error that I get when using a DAX LOOKUPVALUE function is:

Here is the original table:

Here is the Excel logic and expected output for Column D - 'Level 1 (0)':

Formula: = {IF(C2>=0,INDEX(B2:B11979,MATCH(0,C2:C11979,0)),"")}

Here is the Excel logic and expected output for Column E - 'Level 2 (5)':

Formula: {=TRIM(IF(C2>=5,INDEX(B2:B11979,MATCH(5,C2:C11979,0)),""))}

Here is the Excel logic and expected output for Column F - 'Level 3 (10)':

Formula: {=TRIM(IF(C2>=10,INDEX(B2:B11979,MATCH(10,C2:C11979,0)),""))}

And so forth for column G - I:

Formulas for G: {=TRIM(IF(C2>=15,INDEX(B2:B11979,MATCH(15,C2:C11979,0)),""))}

Formulas for H: {=TRIM(IF(C2>=20,INDEX(B2:B11979,MATCH(20,C2:C11979,0)),""))}

Formulas for I: {=TRIM(IF(C2>=25,INDEX(B2:B11979,MATCH(25,C2:C11979,0)),""))}

[v-yuta-msft]

Hi tranba,

LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…) can be used in one-to-many relationship in which search_column should be on the "one" side. Please check the relationship between your tables and check if the search_column is on the "one" side. If this error still happens, could you post the measure you are using and some sample tables for further analysis?

Regards,

Jimmy Tao