Filter for Chart Based on Filter for Page

[pooofy]

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?

[isjoycewang · Accepted solution]

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 · Accepted solution]

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]

thank you again @isjoycewang ! this is super helpful and easy to to understand!

[isjoycewang]

Glad to help! 🙂

[pooofy]

Thank you so much @isjoycewang !!

[isjoycewang · Accepted solution]

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]

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 · Accepted solution]

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]

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 · Accepted solution]

Hi @pooofy ,

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

Demo

[pooofy]

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!

[isjoycewang]

Hi @pooofy ,

I'm not sure why there's no constant line in your visual, are you using the column chart?

It looks the constant line cannot apply in the "Line and Column Combined Chart". Thanks!

[pooofy]

Thank you! 

I have other lines in the chart, e.g. sum of total, average across categories. These are not constant lines. Is there any way to add these lines into a Column chart?

Thank you!

[pooofy]

Thank 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-c... ?

Thank you so much!