Forum Discussion

amiller5's avatar
amiller5
Icon for Helper II rankHelper II
4 years ago
Solved

Returning a value from a lookup value formula

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?
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    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.

8 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      amiller5
      Icon for Helper II rankHelper II
      4 years ago

      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's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        4 years ago

        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"
        )
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      amiller5
      Icon for Helper II rankHelper II
      4 years ago

      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's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        4 years ago

        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.