Forum Discussion

pooofy's avatar
pooofy
Helper I
2 years ago
Solved

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: - V...
Need Help
  • isjoycewang's avatar
    isjoycewang
    2 years ago

    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!

  • isjoycewang's avatar
    isjoycewang
    2 years ago

    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

     

     

  • isjoycewang's avatar
    isjoycewang
    2 years ago

    Hi pooofy ,

     

    You can add constant line in the Analytics Pane, and put your [Measure] in the fx. Thanks.

    Demo

     

     

isjoycewang's avatar
isjoycewang
Solution Supplier
2 years ago

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!

  • pooofy's avatar
    pooofy
    Helper I
    2 years ago

    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!!

    • isjoycewang's avatar
      isjoycewang
      Solution Supplier
      2 years ago

      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