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

    Bailey Strom-Pillar

      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:

       

      table example.PNG

      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!