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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
betski
Frequent Visitor

Filter on year BEFORE selected year in slicer

Hi all,

 

I have a slicer in my report for Year. Most of my visuals need to be filtered on that year, which of course works just fine. However, I have one chart for which I want to show a sum per year for all years BEFORE the selected year. In other words, if the user selects 2017 in the slicer, my chart should show data for 2014, 2015, 2016 (or whichever years I have data for, but only up until 2016). Is this possible? 

 

Thanks in advance!

1 ACCEPTED SOLUTION

HI @betski,

 

Has been testing several scenarios but do not now how your data is fully set up. But check if this post can help you.

 

https://community.powerbi.com/t5/Desktop/Slicer-reverse-selection-in-chart/td-p/96316

 

Regards,

 

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

8 REPLIES 8
MFelix
Super User
Super User

Hi @betski,

 

If you select the slicer then on the top bar select the modeling and then the Edit Interactions all of the visuals will show three options: filter, highlight, do nothing. chose the do nothing (forbidden sign) and filter your visual direcly. The image below shows an example.. Th

 

graphs.png

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



betski
Frequent Visitor

Hi @MFelix and thanks for your reply! Not sure how I would get the filter on the chart to be correct, though... I don't want to hard code it, it should depend on the value in the Year slicer. If the user selects 2016 in the slicer, the chart should show data up until 2015, if they select 2015, the chart should show data up until 2014, etc.

In that case you have to make a measure and used it in your visual.

 

Questions:

 

Do you use a measure or a column in your visual that summarizes?

Where does the year comes from? A date table?

 

Regards

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



betski
Frequent Visitor

The year comes from a time dimension in my tabular SSAS. The chart summarizes a measure (invoice amount) by year (in the time dimension). How do I make a measure to use for this - I will not know beforehand what year the user will select in the slicer?

Can you please show me how your measure invouce amount is calculated?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



betski
Frequent Visitor

There is no special calculation - it is a simple measure added directly from the fact table, summarized per year in a stacked column chart (Time on the axis and Invoice Amount as Value). All I want to do is to add a filter on year saying that the chart should contain only years earlier than the selected value in the Year slicer.

HI @betski,

 

Has been testing several scenarios but do not now how your data is fully set up. But check if this post can help you.

 

https://community.powerbi.com/t5/Desktop/Slicer-reverse-selection-in-chart/td-p/96316

 

Regards,

 

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



betski
Frequent Visitor

That might do the trick! Thanks a lot for posting the link.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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