Forum Discussion
Filter for Chart Based on Filter for Page
Hello~
These are the current visualisations in my dashboard.
1) Slicer for page by Date, using the "Between" Style, i.e. enter start-date, enter end-date
2) Gauge chart with the following:
- Value = Measure that is a calculation of a distinctcount of a column in Table X, with a filter
- Maximum value = Count of distinct years in the Table X
- At the moment, the values for Value and Maximum value are dependent on the Slicer for the page
I would like my gauge chart to only show values for 3 years. These 3 years are to be based on the end-date of the Slicer for the page. E.g. End-date = 09/12/2023, then range of years for the gauge chart to be 2021-2023; end-date = 08/02/2024, then range of years for the gauge chart to be 2022-2024; if the dataset starts from 2021, then if the end-date is 2022, range of years for the gauge chart to be 2021-2022.
How do I get the filter for range of data shown in the gauge chart to be dependent on the end-date in my Slicer for the page?
Hi pooofy,
I create some dummy data for demo as attached: Exercise File
Please create below measure for your chart and replace the [*Measure*] to your own
Gauge Chart Value = var maxyear = MAXX('Table', 'Table'[Date].[Year]) var minyear = maxyear -2 var result = CALCULATE([*Measure*], FILTER( ALL('Table'), 'Table'[Date].[Year] <= maxyear && 'Table'[Date].[Year] >= minyear)) return resultFeel free to let me know if any questions, thanks!
Hi pooofy,
Please try below measure in your column chart:
Column Chart Value = var maxyear = MAXX(ALLSELECTED('Table'), 'Table'[Date].[Year]) var minyear = maxyear -2 var selectedyear = SELECTEDVALUE('Table'[Date].[Year]) var result = IF( selectedyear <= maxyear && selectedyear >= minyear, CALCULATE([Measure], ALL('Table'[Date]), 'Table'[Date].[Year] = selectedyear), BLANK()) return result
12 Replies
Hi pooofy,
I create some dummy data for demo as attached: Exercise File
Please create below measure for your chart and replace the [*Measure*] to your own
Gauge Chart Value = var maxyear = MAXX('Table', 'Table'[Date].[Year]) var minyear = maxyear -2 var result = CALCULATE([*Measure*], FILTER( ALL('Table'), 'Table'[Date].[Year] <= maxyear && 'Table'[Date].[Year] >= minyear)) return resultFeel free to let me know if any questions, thanks!
Sorry, I realise I have another chart ther requires the same adjustment for years.
It is a column chart with Years as the X-axis, e.g. 2020, 2021, 2022, 2023.
Could I please ask how I can get the chart to only show 3 years of data, with the last year = the max year in the page Slicer?
Thank you!!
Hi pooofy,
Please try below measure in your column chart:
Column Chart Value = var maxyear = MAXX(ALLSELECTED('Table'), 'Table'[Date].[Year]) var minyear = maxyear -2 var selectedyear = SELECTEDVALUE('Table'[Date].[Year]) var result = IF( selectedyear <= maxyear && selectedyear >= minyear, CALCULATE([Measure], ALL('Table'[Date]), 'Table'[Date].[Year] = selectedyear), BLANK()) return result
Thank you so much isjoycewang !!
thank you again isjoycewang ! this is super helpful and easy to to understand!
Glad to help! 🙂
