Returning a value from a lookup value formula

[amiller5]

I have 2 different files - they are connected in relationships by Account name but there may be multiple account names in the "Service 3 - Opps" file. 

This is the column measure I have started: 

Has an opportunity? =

IFERROR (

IF (

LOOKUPVALUE (

'Service 3 - Contract Opps'[Account Name],

'Service 4 - Contract accts'[Account Name], 'Service 4 - Contract accts'[Account Name]

),

1,

0

),

0

)

 

essentially if the account is in the opportunity file, we need to identify it as having an opportunity with a 1.00 value. And if it is not, a 0. Right now it is returning all 0's.

 Am I missing something?

[Ashish_Mathur]

Hi,

Write this calculated column formula in the  Service 4 - Contract accts table

=LOOKUPVALUE('Service 3 - Contract Opps'[Account Name],'Service 3 - Contract Opps'[Account Name], 'Service 4 - Contract accts'[Account Name])

Does this work?

[amiller5]

This works but it brings in the lookup value text of the account name. I want to create a column where if the name appears in the other table, it gets a value of 1.00 and if not, it get a value of 0.00.

[Ashish_Mathur · Accepted solution]

Hi,

Try this

=if(isblank(LOOKUPVALUE('Service 3 - Contract Opps'[Account Name],'Service 3 - Contract Opps'[Account Name], 'Service 4 - Contract accts'[Account Name])),0,1)

Hope this helps.

[ryan_mayu]

@amiller5 

try this

IF (

ISBLANK(LOOKUPVALUE (

'Service 3 - Contract Opps'[Account Name],

'Service 4 - Contract accts'[Account Name], 'Service 4 - Contract accts'[Account Name]

)),

0,

1

)

OR 

IF (

LOOKUPVALUE (

'Service 3 - Contract Opps'[Account Name],

'Service 4 - Contract accts'[Account Name], 'Service 4 - Contract accts'[Account Name]

)=“”,

0，1））

[amiller5]

I think it is more of an "IfError" expression because the corresponding lookup just may not be there, not necessarily blank. but when I try to input that type of formular I get an error "Expressions that yield vairant data-type cannot be used to define calculated columns."

Has an opportunity? =

IFERROR (

LOOKUPVALUE (

'Service 3 - Contract Opps'[Account Name],

'Service 4 - Contract accts'[Account Name], 'Service 4 - Contract accts'[Account Name]

),

0

)

[ryan_mayu]

@amiller5 

you can have two data type in one column. i guess the account name is text.

you can try this

Has an opportunity? =

IFERROR (

LOOKUPVALUE (

'Service 3 - Contract Opps'[Account Name],

'Service 4 - Contract accts'[Account Name], 'Service 4 - Contract accts'[Account Name]

),

"0"

)

[amiller5]

This also brings in the account name when the Iferror expression with the lookup is true. I want it to bring in a 1.0 value

[ryan_mayu]

@amiller5 

pls try this

IF ( ISBLANK(LOOKUPVALUE (

'Service 3 - Contract Opps'[Account Name],

'Service 4 - Contract accts'[Account Name], 'Service 4 - Contract accts'[Account Name])),0,1)