Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GellaiTamas
Helper I
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.  

 

whatif_search.JPG

 

 

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

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

View solution in original post

10 REPLIES 10
v-yuezhe-msft
Employee
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.

Hi @v-yuezhe-msft,

 

The following 2 tables are in use:

 

Uniform1

uniformcost.JPG

 

 

 

 

 

 

 

 

 

 

Vehicle_costs


vehicletype.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

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

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

@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

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.

@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

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

@v-yuezhe-msft

 

Hi Lydia,

 

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

 

Thanks,

Tamas

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

@v-yuezhe-msft

 

It works. Thanks a million, Lydia!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.