3 Replies Latest reply on Nov 22, 2017 11:18 PM by Norbert Maijoor

# Getting the value of a column given another column meets a requirement

I am currently working with a data set that contains information about power potentials for wind farms based on their location-- onshore or offshore, distance to the load/shore, and, if offshore, the depth of the water at the wind farm location. My table looks like this:

As you can see, Australia has three Depth Classes: deep, shallow, and transitional (and total, which, as the name implies, totals the power potentials for all depths at a given distance to the shore). Wind classes are listed for each depth class, at three distances from the shore: near, intermediate, and far.

What I am trying to do is determine which depth class (excluding "total") and distance-to-shore combination has the highest power potential; for Australia, you can see that occurs when Depth Class = "deep" and at an intermediate distance from the shore (2,259.66 GW). I am able to get the *value* of the highest power potential by creating the calculated field "Max Power By Distance to Shore" as

`MAX(MAX([Near : 5-20 Nautical Miles Total], [Far : 50-100 Nautical Miles Total]),[Intermediate : 20-50 Nautical Miles Total])`

and displaying the measure as a Maximum, to find the max across all depths for that distance. However, what I want is to display only the depth class and the distance of this maximum value-- which would require identifying the value of the Depth Class column at the row where the maximum value occurs, and the column the maximum value is in.

I suspect I'll have to create two calculated fields-- one to find the depth class and one to display the column name. I was able to create the second one, displaying the distance from the shore containing the maximum power potential using:

`IF(MAX([Near : 5-20 Nautical Miles Total]) >= MAX([Intermediate : 20-50 Nautical Miles Total]) AND MAX([Near : 5-20 Nautical Miles Total]) >= MAX([Far : 50-100 Nautical Miles Total])) `

`THEN "Near: 5-20 Nautical Miles" `

`ELSEIF (MAX([Intermediate : 20-50 Nautical Miles Total]) >= MAX([Far : 50-100 Nautical Miles Total]) AND MAX([Intermediate : 20-50 Nautical Miles Total]) >= MAX([Near : 5-20 Nautical Miles Total])) `

`THEN "Intermediate: 20-50 Nautical Miles" `

`ELSEIF(MAX([Far : 50-100 Nautical Miles Total]) >= MAX([Intermediate : 20-50 Nautical Miles Total]) AND MAX([Far : 50-100 Nautical Miles Total]) >= MAX([Intermediate : 20-50 Nautical Miles Total])) `

`THEN "Far: 50-100 Nautical Miles" END`

However, I am not sure how to look up the depth class, given the maximum value. I have been trying to do something where I look up the row containing the maximum value, and then try to get the value of the Depth Class column at that row, but I'm having trouble figuring out how to best use Tableau's table lookup functions. Does anyone have any suggestions?

Thank you!

• ###### 1. Re: Getting the value of a column given another column meets a requirement

Hi Bailey,

Find my approach as reference below and stored in attached workbook version 10.3 located in the original thread.

2. M1. Max: {Fixed [Depth Class]:max([Pivot Field Values])}

3. D1. Display: if [Pivot Field Values]= [M1. Max] then [Pivot Field Names] END

4. Drag required objects to the indicated locations and exclude total from Depth Class and Null from D1. Display on Filters

Regards,

Norbert

• ###### 2. Re: Getting the value of a column given another column meets a requirement

Hi Norbert,

Thank you for the reply! Unfortunately, I am unable to download your workbook as I only have Tableau Public. However, I replicated the steps described above and found that they do not seem to work; your value for M1.Max returns the maximum across all countries. I found that fixing Countries as well as Depth Class is required to this issue, as this finds the maximum value for each country at each depth rather than simply at each depth.

Once this change is made, D1.Display shows which distances correspond with the maximum power at each depth. I would like to narrow this down to a single, maximum, depth/distance pairing per country.

• ###### 3. Re: Getting the value of a column given another column meets a requirement

Hi Bailey,

Please adjust measure M1. Max: {Fixed [Depth Class], Countries :max([Pivot Field Values])}. it will give max per country

Regards,

Norbert