cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

One selection in slicer filtering two values

Hi!

Im my report, i need to filter the year selected in slicer and year -1. Its something like this:

  

 

SELECT    RETURN

2018       2018
               2017

 

2017       2017
               2016

 

2016       2016
               2015

 

2015       2015
               2014

 

My table is created based in SUMMARIZECOLUMNS , and have a column with year. Someone have any solution to this?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: One selection in slicer filtering two values

Hi@ guilherme_gq

You can use UNION() and  DISTINCT() function to create an intermediate table like below:

intermediate Table = DISTINCT(UNION(VALUES('Table'[year]),VALUES('YEAR TABLE'[return])))

And create relationship between two table by the intermediate table

9.png

 

Then drag filed into slicer

For example ,when we select 2018 it return 2018 and 2017

 

10.png

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Highlighted
Solution Specialist
Solution Specialist

Re: One selection in slicer filtering two values

@guilherme_gq To get both years you could use SELECTEDVALUE(year) and SELECTEDVALUE(year)-1 to get both the years.  If this doesn't answer your question I need more details on the setup to be able to better answer the question.

Highlighted
Helper I
Helper I

Re: One selection in slicer filtering two values

Mnayar,

 

I create a table like the first post. But im trying to filtering based in relationship with two tables. For example:

 

     YEAR TABLE

SELECT    RETURN

2018       2018
2018       2017

 

2017       2017
2017       2016

 

2016       2016
2016       2015

 

2015       2015
2015       2014

 

 

But when i try to create relationship between the new table and the table with each row of sell, i recieve this message:

 

"You Can´t create a relationship between these two columns because one of the columns must have unique values."

Highlighted
Solution Specialist
Solution Specialist

Re: One selection in slicer filtering two values

create a date dimention and hook these 2 tables to that date dimension.

Highlighted
Community Support
Community Support

Re: One selection in slicer filtering two values

Hi@ guilherme_gq

You can use UNION() and  DISTINCT() function to create an intermediate table like below:

intermediate Table = DISTINCT(UNION(VALUES('Table'[year]),VALUES('YEAR TABLE'[return])))

And create relationship between two table by the intermediate table

9.png

 

Then drag filed into slicer

For example ,when we select 2018 it return 2018 and 2017

 

10.png

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper I
Helper I

Re: One selection in slicer filtering two values

Good job, Lin! Thanks for your help!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors