Forum Discussion

dkehoe32's avatar
dkehoe32
Helper I
8 years ago

syntax is incorrect

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      
Need Help
dkehoe32's avatar
dkehoe32
Helper I
8 years ago

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's avatar
v-huizhn-msft
Microsoft Employee
8 years ago

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's avatar
    dkehoe32
    Helper I
    8 years ago

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

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Microsoft Employee
      8 years ago

      Hi dkehoe32,

      Please share a screenshot shown the DAX you used?

      Thanks,
      Angelia

      • dkehoe32's avatar
        dkehoe32
        Helper I
        8 years ago

        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])
          ))