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

Hi Anonymous,

 

The following 2 tables are in use:

 

Uniform1

 

 

 

 

 

 

 

 

 

 

Vehicle_costs


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The task is to multiply the number of vehicles by the uniform cost - however when Trailer or Trailer (USED) is chosen as the vehicle, uniform cost will be 0.

 

The above code resulted in a syntax error (PBI doesn't tell anything else) - but my gut feeling is that my concept itself is wrong. I'm pretty new to DAX, I'd rather call my approach as a playaround instead of being conscious.

 

How would you solve this?

 

Thanks,

Tamás

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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      GellaiTamas,

      Please check if the following DAX returns your expected result.

      Uniform cost = IF(ISERROR(SEARCH("Trailer",'Vehicle_costs'[Vehicle])),CALCULATE(SUM('Uniform1'[Annual uniform cost]))*[# of vehicle Value],0)



      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