One selection in slicer filtering two values

[guilherme_gq]

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?

[v-lili6-msft · Accepted solution]

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

Then drag filed into slicer

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

Best Regards,

Lin

[guilherme_gq]

Good job, Lin! Thanks for your help!

[Anonymous]

@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.

[guilherme_gq]

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."

[Anonymous]

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