1 Reply Latest reply on Mar 4, 2016 3:49 AM by Simon Runc

# Lookup values in data based on a parameter

I have Car model name parameter in my workbook and want to lookup the Car Company name (1:N relationship) which will be used by a calculated field. I don't want/need to add Company name to the view. So basically I want within the calculated field to lookup the company name related to the Car model in the parameter so that I can perform further calculations.

• ###### 1. Re: Lookup values in data based on a parameter

hi Narbuka,

So there are a couple of ways to do this..my fave is to use sets...and the method is very well explained in this great video on cool things you can use sets for Think Data Thursday - November 20 - Let's talk about Sets Baby!

I've dummied up some data, where we have 2 companies and they have several car models each. In the solution when we select a car model from the parameter, every row of data with the 'company' of that car model goes 'in' the set (as we can access this in other calculated fields)

So we create the Set off Company, and use the following condition

MAX(IIF([Car Model]=[Select Model],1,0))=1 The way this works (and the video explains it better than me!)...a set condition must be aggregated and a boolean expression. So the IIF([Car Model]=[Select Model],1,0) is a Row-Level calculation which puts a 1 or 0 against each row depending on if the Car Model is the same as the parameter selection. then at the Company level (as this is the level we've created the set at) it looks if the MAX (this is the aggregate bit) is =1 (this is the boolean bit), and if it does the company goes in the set.

We could also have done this with LoD, but I really like this method.

Hope this does the trick and makes sense...if not please post back