Forum Discussion

guilherme_gq's avatar
guilherme_gq
Helper I
8 years ago
Solved

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?

 

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    8 years ago

    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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      guilherme_gq
      Helper I
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

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

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    8 years ago

    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