7 Replies Latest reply on Feb 6, 2019 8:27 AM by Shinichiro Murakami

    Display the Dimension value for maximum measure value

    Michael Lock

      I have a larg(ish) table (Roughly 2,400,000 rows) of hourly measurements of NO2 (Nitrogen Dioxide) from 99 different stations (locations where NO2 is measured).  I need to display;

       

      • The average, yearly station average (i.e. average the hourly measurements for each station and then average all of the stations)
      • The maximum station average for each year
      • The minimum yearly station average for each year

       

      I want to display the name of the station as a label for each year for the maximum and minimum values.  This is where I'm stuck.

       

      I've got the average yearly station average with this calculated field {INCLUDE [Station Id] : AVG([Hourly NO2 ppb])}

      I've got the max station average with this calculated MAX([Station Average NO2 ppb])

      I've got the min station average with this calculation Min([Station Average NO2 ppb])

       

      I'm displaying the three measures in a vis like this;

      Capture.PNG

       

      I don't know how to write a formula which will derive the station name for the max and min value for each year.  Can someone help with this?

       

      I've tried IF MAX({ INCLUDE [Station Id] : AVG([Hourly NO2 ppb])}) = [Max Station Average NO2 ppb] THEN ATTR([Name]) END.  When I drag that on to the label tile asterisks are displayed at each data point.

       

      I Found this Find MAX(value) at different levels of detail, return string where category is max which got me a little closer when I put the 2nd calculated field as a Tooltip (it puts the same i.e. station name of the max average station on all data points).