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

GellaiTamas's avatar
GellaiTamas
Helper I
8 years ago

Anonymous

 

Hi Lydia,

 

Thanks for the code. Unfortunately it produces the following error:

A single value for column 'Vehicle1' in table 'Vehicle_costs' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

 

BR,

Tamás

Anonymous's avatar
Anonymous
Not applicable
8 years ago

GellaiTamas,

Right click your table, choose "New Column" and apply the DAX.

Regards,
Lydia

  • GellaiTamas's avatar
    GellaiTamas
    Helper I
    8 years ago

    Anonymous

     

    Hi Lydia,

     

    Unfortunately I get the same error message when applying the DAX for a new column.

     

    Thanks,

    Tamas

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

  • GellaiTamas's avatar
    GellaiTamas
    Helper I
    8 years ago

    Anonymous

     

    It works. Thanks a million, Lydia!