Forum Discussion
Filter for Chart Based on Filter for 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
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 result
Feel 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 resultThank you again! sorry, i was trying to see if i could adapt your solution to another issue i was facing, but i couldn't. Sorry, would you know how to solve the issue in https://community.fabric.microsoft.com/t5/Desktop/Need-help-with-DAX-measure-error-for-Counta-with-criteria/m-p/3439295#M1140906 ?
Thank you so much!
Hi isjoycewang ,
Thank you so much for the help so far!
I'm so sorry, i am stuck again.
I am trying to create a line, showing the average for the 3 years filtered out, in the column chart.
For example:
- For 2021-2023, the average count = 19/3 = 6.3, so a straightline at 6.3 is shown in the graph.
- For 2020-2022, the average count = 14/3 = 4.6, so a straighline at 4.6 is shown in the graph
At the moment, I am stuck as the line I have is calculated based on the Year it is at, thus showing different values across the years.
Thank you again!