cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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

1 ACCEPTED SOLUTION
Microsoft Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 REPLIES 10
Microsoft Employee

@GellaiTamas,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

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

Microsoft Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

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.

Vehicle: Hyundai HD72

Number of vehicles: 2

=>  225,4x2 = 450,8

2.

Vehicle: Trailer

Number of vehicles: 2

=>  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!

Microsoft Employee

@GellaiTamas,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

@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

Microsoft Employee

@GellaiTamas,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

@v-yuezhe-msft

Hi Lydia,

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

Thanks,

Tamas

Microsoft Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper I

@v-yuezhe-msft

It works. Thanks a million, Lydia!