SEARCH + What-if + multiple tables

[GellaiTamas]

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

[v-yuezhe-msft]

@GellaiTamas,

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

Regards,
Lydia

[GellaiTamas]

Hi @v-yuezhe-msft,

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

[v-yuezhe-msft]

@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]

@v-yuezhe-msft,

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!

[v-yuezhe-msft]

@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]

@v-yuezhe-msft

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

[v-yuezhe-msft]

@GellaiTamas,

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

Regards,
Lydia

[GellaiTamas]

@v-yuezhe-msft

Hi Lydia,

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

Thanks,

Tamas

[v-yuezhe-msft · Accepted solution]

@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]

@v-yuezhe-msft

It works. Thanks a million, Lydia!