Forum Discussion

tdoth1995's avatar
tdoth1995
Regular Visitor
3 years ago
Solved

Using LOOKUPVALUE with multiple conditions, returns one value if only one condition met

Not sure of the best way to describe the problem or even how to approach solving this but let me try my best.    I'm using LOOKUPVALUE to pull a value based on two columns: [Defect] and [Location] ...
Need Help
  • jgeddes's avatar
    jgeddes
    3 years ago

    You can try to add a conditional 'location' variable in your calculated column.
    Something along the lines of...

    Average = 
    var _location =
    IF(
        ISBLANK(
            LOOKUPVALUE('Table A'[Location], 'Table A'[Name], 'Table B'[Name], 'Table A'[Location], 'Table B'[Location])
        ),
        "Other",
        [Location]
    )
    Return
    LOOKUPVALUE(
        'Table A'[Avg],
        'Table A'[Name], 'Table B'[Name],
        'Table A'[Location], _location
    )

    Basically you are performing the lookup twice. The first time to see if the location returns a blank. The second time returns the rework time using 'Other' if needed.

jgeddes's avatar
jgeddes
Super User
3 years ago

You can try to add a conditional 'location' variable in your calculated column.
Something along the lines of...

Average = 
var _location =
IF(
    ISBLANK(
        LOOKUPVALUE('Table A'[Location], 'Table A'[Name], 'Table B'[Name], 'Table A'[Location], 'Table B'[Location])
    ),
    "Other",
    [Location]
)
Return
LOOKUPVALUE(
    'Table A'[Avg],
    'Table A'[Name], 'Table B'[Name],
    'Table A'[Location], _location
)

Basically you are performing the lookup twice. The first time to see if the location returns a blank. The second time returns the rework time using 'Other' if needed.

  • tdoth1995's avatar
    tdoth1995
    Regular Visitor
    3 years ago

    Thanks for the quick response. Could you elaborate a little further on a couple of points:

    1. 

    Average = 
    var _location =

     Where is this coming from? 

    2. IS Table A the table that I'm trying to perform the lookupvalue from and Table B the table that I have the calculated column in? Or vice-versa.

    3. 

    'Table A'[Avg],


    Is this coming from the "Average" value that was just calculated in the introduced _location variable?

    Just for clarity for me: Can you refer to the table being looked up as "Table Lookup" and the table which the calculated column is being put into as "QC Database".


    Thanks again!

  • tdoth1995's avatar
    tdoth1995
    Regular Visitor
    3 years ago

    Never mind! I see where my mistake was, you were just shortening some of the variables I had given you and the variables from the two tables were differing in my formula. 

    This solution worked quite well for what I needed it for. Thank you!