Forum Discussion

Jon54's avatar
Jon54
Frequent Visitor
4 years ago
Solved

using max column value as filter in scatter chart

Hello,

I have data from following URL:

https://fingertips.phe.org.uk/api/all_data/csv/by_indicator_id?indicator_ids=811%2C%2022001%2C%2030301&child_area_type_id=402&parent_area_type_id=6

The 3 datasets returned for different Indicator IDs use different 'Time period' formats. The 'Time period sortable' field instead is more user friendly. I want to be able to create a line chart showing trend and a map and scatter chart showing just the latest data for each indicator: 

How can I force the scatter chart and map to use the last year for which the different indicators have data as sometimes this is 20200000 and sometimes this is 20210000. I have tried chiclet slicer with forced selection but I want to hide that and when I do so, the scatter chart and map don't always update when the indicator name is changed. Without such a slicer or filter in place the values for the indicators are being summed over all the available years of data for each Area.

Any advice greatly appreciated

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    It would help if you could show us the structure of the dataset. 
    However, try creating a measure along the lines of:

    Filter year = CALCULATE (MAX(Table[Time period sortable]), ALL(Table[Time period sortable)))

    Add the Timeperiod sortable field to the filter pane for each visual. In the dropdown, select TopN and set the value to greater or equal to 1

2 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    It would help if you could show us the structure of the dataset. 
    However, try creating a measure along the lines of:

    Filter year = CALCULATE (MAX(Table[Time period sortable]), ALL(Table[Time period sortable)))

    Add the Timeperiod sortable field to the filter pane for each visual. In the dropdown, select TopN and set the value to greater or equal to 1