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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Signore_Ands
Advocate II
Advocate II

Chart - Set Max Value for Axis Based on Slicer Value

There are several similar posts about this already but none that really covers what I am looking for.

I have a report page with slicers for Reporting Year and Reportig Month.  These slicers filter several Cards on the page to give the relevant values for the selected reporting period and the corrresponding YTD and PYTD values.

I then have line charts on the page to show the trend over the current and previous years.   
These charts are decoupled from the slicers, via Edit Interactions, because I don't want the data filtered to just give one month's worth of data.

The report works fine as is except that I now need to be able to limit the inclusion of future months data in the line charts - e.g. if I am reporting for February I don't want to see any data already captured for March (assume I am in March now, reporting for the previous month). 

I would also like to start showing a Last 12 Month view, so report up to the Max of the Selected Rpeoritng Period and include the previous 11 months.  I have a last 12 month flag in my calendar month dimension that would facilitate this - if I can get around how to use the slicers.

I think the essence of the problem is that I need to rely on the values in the slicers but can't allow the slicers to filter my data as they normally would - so a February value in the slicer should give me January and February data.

Using a From - To -  slicer works for the Line Chart but then doens't work for the Card Visuals that just need to return a single month's data.

It feels like part of the answer is to have a filtered calendar_month table that only includes months that are relevant for the reporting period - but I am struggling to get a table like this to work, being linked to parameters / slicer values.

Any guidance would be appreciated.  Thanks.

6 REPLIES 6
Syndicate_Admin
Administrator
Administrator

try to get the segmenters to filter the line graphs, but the cards calculate which is the maximum month selected and according to that they obtain the value you need to show.

Gracias, jairoaol - I've not used segmenters before; where do I find those settings?

 

amitchandak
Super User
Super User

@Signore_Ands , if you select a month and want to display more months then you need an independent table

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

Thanks Amit - definitely an interesting video, and worth remembering for future challenges, but it's not giving me what I am looking for - having one filter value that updates the card (value for the month) and the chart (where the axis needs to adjust to show all data from the begining of the previous year up to the reporting month of this year.)  

JGroothedde
Advocate II
Advocate II

Hi there,

Wouldn't this issue be resolved by setting a TOP N filter on the card visuals (TOP N month) and a relative date filter on other visuals where you don't want to show the current month (date is in X last calendar months) for example? You can do this in the filter pane.


Thanks for the suggestion - it sounded promising but the Top N is not going to work for this measure, which is just a count of all incidents, the filter is failing becasue I don't really have anything to put in the By Value field.

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.