syntax is incorrect

[dkehoe32]

Im trying to convert this excell formula into BI to get the data for the column to match up with the others but im not sure what the correct formula in BI would be

[Greg_Deckler]

LOOKUPVALUE is defined here:

https://msdn.microsoft.com/en-us/query-bi/dax/lookupvalue-function-dax

Basically, you generally want 3 parameters:

1. Result column - what you are trying to return
2. Search column - the column to search for a value
3. Search value - the value to search for in the Search column.

The syntax you have is odd. The third parameter is nonsensical. You have a mash-up of a column reference and then a text string. Perhaps if you can describe what you are trying to achieve?

[dkehoe32]

Im trying to take this column from excell and move it into my BI table.  I can get it into BI without the "2017 Part Number" but it just gives me blank cells instead of the text.  The excell formula takes data from other excell sheets and thats essentially what im trying to do in BI

[v-huizhn-msft]

Hi @dkehoe32,

You can create a measure using the formula to get the expected result.

=
IF (
    ISBLANK ( LOOKUPVALUE ( [D_colum], [column], "I2" ) ),
    "2017 Part Number",
    "New 2018 Part numbers" && LOOKUPVALUE ( [D_colum], [column], "I2" )
)

Thanks,
Angelia

[dkehoe32]

I just tried that formula and it says "Expressions that yield variant data type cannot be used to define calculated columns"

[v-huizhn-msft]

Hi @dkehoe32,

Please share a screenshot shown the DAX you used?

Thanks,
Angelia

[dkehoe32]

New for 2018 Part Number = IF(ISBLANK(LOOKUPVALUE('New 2018 Instrument Part Numbers'[Jason comment],'New 2018 Instrument Part Numbers'[Product Code],'Oracle Transactions Instrument Master'[Material]),"2017 Part Number","New 2018 Part Numbers" && LOOKUPVALUE('New 2018 Instrument Part Numbers'[Jason comment],'New 2018 Instrument Part Numbers'[Product Code],'Oracle Transactions Instrument Master'[Material])
  ))

[v-huizhn-msft]

Hi @dkehoe32,

In your formula,  the data type of search_columnName and search_value are the same? They must be same and compared.

Thanks,
Angelia