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

     

     

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

 

 

  • pooofy's avatar
    pooofy
    Helper I
    2 years ago

    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!

    • isjoycewang's avatar
      isjoycewang
      Solution Supplier
      2 years ago

      Hi pooofy ,

       

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

      Demo

       

       

      • pooofy's avatar
        pooofy
        Helper I
        2 years ago

        Hi isjoycewang ,

         

        I tried to insert a line, but unfortunately, my chart does not show an option for "Constant Line", it only has Trend line and X-Axis Constant Line.

         

        Do you know if there is another way to add a Y-Axis Constant line?

         

        I am using the Line and Column chart. There is a field for Lin y-axis. However, if I insert my measure into that field, the result shows the average, taking the year in the chart as the max year, e.g. for 2021, the average is calculated with 2021 as the max year, instead of the max year of the entire page. 

         

        Is there an alternative way to create a constant y-line based on the average with the max year as the last year for the chart?

         

        Thank you!