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
Jon54
Frequent Visitor

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: 

Jon54_0-1661266047594.png

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

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

2 REPLIES 2
Jon54
Frequent Visitor

Thanks @PaulDBrown , worked perfectly. Jon

PaulDBrown
Community Champion
Community Champion

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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.