Forum Discussion
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] which gives me a numerical value. This works fine, but I only have data for around 80% of the data. i.e. the defect location may have 50 total values but I've only collected data for 10.
This is how the formula looks like:
This is the table I'm pulling the lookup value from:
What I'm trying to achieve is if the lookup value meets the first requirement of the "Defect Name" column, but not the "Defect Location" column, it automatically defaults to using the "Defect Location" value of other.
For example, I'm looking to pull up a value of
[defect]"Open Lamination" and [Locaion]"Yoke".
Since there is no value for this in my lookuptable, I want it to automatically refer to using the value listed in
[defect]"Open Lamination" and [Location]"Other"
If it helps, this value is just an average of the values listed for that particular defect. Let me know if I've explained that well but not sure which functions I would even use to approach this type of problem. Let me know if there's anything I need to give clarification on that make it easier to support me.
Thank you:)
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.
3 Replies
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.
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!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!