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, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
byggarebob
Regular Visitor

Variable filter range based on slicer single selection

Hi,

 

I'm building a report which will be used on an Ipad and I want to minimize the number of slicer selections needed for the user to make it more user friendly. I have a year slicer with single selection and I want to keep it that way.

 

I have a line chart where I want to show the selected year AND the year before. Problem is obviously with the single selection slicer. There is a "periodic table" that I use for all calendar dimensions and this is the source of the Year slicer in the report (this slicer is disconnected from the chart in question). In this periodic table I have created the following measures to try to solve the problem:

 

Selected year = SELECTEDVALUE(Periodic_table[Year])       <-  collecting the year selected in the slicer

Prior year = [Selected year]-1

 

Filter = IF(

    VALUES(Periodic_table[Year])>=[Prior year] && VALUES(Periodic_table[Year]) <= [Selected year],
    1,0)
 
I put the Filter measure as a filter in the chart and set it to only include "1". However, the chart does not seem to respond to this filter, all years are currently shown in the chart. (When I set the filter to "0" all lines disappear from the chart)
 
The chart looks like this and has the following settings:
Month and year comes from the periodic table and the data (POS visit) comes from a parameter table.
 
byggarebob_0-1675344684931.pngbyggarebob_1-1675344734076.png

 

byggarebob_2-1675344898189.png

 

I hope its somewhat easy to follow! 😄

 

Would highly appreciate any help here.

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@byggarebob , Based on what I got

 

In such a case slicer, need to be on an independent year/date table and then you can have measure like

 

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Year])
var _min = _max -1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Year] >=_min && 'Date'[Year] <=_max))

 

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@byggarebob , Based on what I got

 

In such a case slicer, need to be on an independent year/date table and then you can have measure like

 

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Year])
var _min = _max -1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Year] >=_min && 'Date'[Year] <=_max))

 

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Your suggested solution worked! I only had to add a slicer linked to year in the independent date table and then sync that slicer to the "year"-slicer of the main date table. Thanks alot!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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