Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Common Slicers for Multiple Tables

Hi All,

 

Provided my report layout below.

I am using 3 Tables. Provided the columns present in those tables at the right hand side.

 

I have 3 columns common across the 3 tables and those are Filter 1 , Filter 2 and Filter 3.

I planned to give these 3 columns in Slicers across the reports(Which means the common slicers should get affected in all the 3 widgets when applied).

 

I am struggling in relationship part. How to provide relationship? How to give common slicers for this scenario?

I thought of creating calculated table using union all for common fields but not having clarity on how to achieve that.

Then thought of trying user relationship but also not sure whether that will lead to what i am expecting.

 

So please help me on how to create common slicers.

 

Thanks in Advance

Prakash

Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi Anonymous ,

     

    This is what you must do in order to have a single slicer for each of the filter tables, there is no problem in having 6 tables and 9 relationships, this is a star schema where you have dimension tables (you filtering tables) and the Data tables, in order to have single slicers you need to have the relationships setup otherwise you need to create 9 separate slicers.

     

    There is no issue on having this relationships created, one good practice that you should do is to hide from the visualization the filter columns on the your other 3 tables that way when you need to use them on your visualizations you will select the correct columns (the ones that are on the one side of the relationship).

10 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    Hi Anonymous ,

     

    Create an additional table for each of your filter columns (3 new tables) with distinct values then relate this 3 tables with the other 3 so you will get a relationship between each filter table (one side) with the other tables (many side).

     

    Now use this new tables as your slicers.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , You create three tables like these for each field and join them with all three tables and analyze together

    New table

    Field 1 =distinct(union(all(Table1[filter 1]),all(Table2[filter 1]),all(Table3[filter 1])))

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak MFelix ,

       

      Thanks for your reply.

       

      I created 3 Tables as per your suggetion with distinct values of the filters columns and named those tables as Table1, Table2 and Table3 respectively. Show below.

      Sheet1, Sheet2 and Sheet3 is the main data set.

       

      Now I am confused on how to join these 3 tables with other 3 tables to achieve Common filters across 3 tables?

      Could you guys please provide me direction on how to proceed further.

       

      Thanks in Advance

      Prakash