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 talking about (what-if slicer)

 

Uniform costs for the vehicle drivers are different in different countries but are the same no matter which vehicle type we choose - EXCEPT if we choose "Trailer" because the uniform cost in this case is 0.

 

So I'd like to multiply the uniform cost of the chosen country by the number of vehicles but calculate with 0 if "trailer" is chosen for the vehicle type.  

 

 

 

This is the code I use - which results in syntax error:

 

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

Could you please suggest how to tackle this?

 

Thanks,

Tamás

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

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    GellaiTamas,

    What error message do you get? Could you please share sample data of your tables for us to analyze?

    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