cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
guilherme_gq
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
v-lili6-msft
Community Support
Community Support

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
v-lili6-msft
Community Support
Community Support

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.

Good job, Lin! Thanks for your help!

Anonymous
Not applicable

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

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
Not applicable

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.