Forum Discussion

GellaiTamas's avatar
GellaiTamas
Helper I
8 years ago
Solved

SEARCH + What-if + multiple tables

Hi,   Here is the situation: I'd like to create a measure which is based on: - what country the user chooses (dropdown) - what vehicle type is chosen (dropdown) - how many vehicles are we talki...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    GellaiTamas,

    Create the following column in Vehicle_cost table.

    CheckTrailer = IF(ISERROR(SEARCH("Trailer",Vehicle_costs[Vehicle])),0,1)

    Then create the following measures in Vehicle_cost table.

    Measure = MAX(Uniform1[Annual])
    Uniform Cost = IF(MAX(Vehicle_costs[CheckTrailer])=1,0,[Measure]*[# of vehicle Value])

    Regards,
    Lydia

Anonymous's avatar
Anonymous
Not applicable
8 years ago

GellaiTamas,

How do you relate the first table to the second table? Could you please post expected result in table based on  the above sample data?

Regards,
Lydia

GellaiTamas's avatar
GellaiTamas
Helper I
8 years ago

Anonymous,

 

Thing is that the 2 tables are not related. There are no columns which could be anyhow connected (they only have those 2 columns you can see).

 

User inputs:

- 'Country'[CountryName] - dropdown list (this comes from a 3rd table)

- 'Vehicle_costs'[Vehicle] - dropdown list

- number of vehicles - input value (I use a What-if scenario for this)

 

Then the number of vehicles entered is multiplied by the uniform cost ('Uniform1'[AnnualUniformCost]. This is easy and it works. Things don't work if I try to apply 0 as the uniform cost if "Trailer" is chosen as [Vehicle].

 

Expected result based on the tables I copied:

 

1.

Country: Canada

Vehicle: Hyundai HD72

Number of vehicles: 2

Uniform cost (Canada): 225,4 GBP

=>  225,4x2 = 450,8

 

2.

Country: Canada

Vehicle: Trailer

Number of vehicles: 2

Uniform cost (Canada): 225,4 GBP

=>  0 = 0 (because no uniform is needed for a trailer)

 

This should be a simple IF function with a SEARCH function - at least this is how it works in Excel (IF(ISERROR(SEARCH))).

I just can't figure out how to put it all together.

 

Thanks!