Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
pooofy
Helper I
Helper I

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?

3 ACCEPTED SOLUTIONS
isjoycewang
Super User
Super User

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

 

isjoycewang_0-1694421188731.png

 

 

Feel free to let me know if any questions, thanks!

View solution in original post

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_0-1694498467711.png

 

View solution in original post

Hi @pooofy ,

 

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

Demo

 

isjoycewang_0-1696405683783.png

 

View solution in original post

12 REPLIES 12
pooofy
Helper I
Helper I

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

Glad to help! 🙂

pooofy
Helper I
Helper I

Thank you so much @isjoycewang !!

isjoycewang
Super User
Super User

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

 

isjoycewang_0-1694421188731.png

 

 

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 result

 

isjoycewang_0-1694498467711.png

 

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!

Hi @pooofy ,

 

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

Demo

 

isjoycewang_0-1696405683783.png

 

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!

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!

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!

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!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.