Common Slicers for Multiple Tables

[Anonymous]

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

[amitchandak]

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

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

[MFelix]

Hi @Anonymous ,

Just drag and drop from the filter tables columns to the tables columns or use the manage relationship option:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships

Be aware that you need to have it in the correct table.

[Anonymous]

Hi @MFelix ,

I am aware of creating relationship but my question is i have 3 tables newly created and i want all the 3 new table should be in relationship with all the old tables. 3*3 = 9 and i dont think i should give 9 relationships among these 6 tables.

Regards,

Prakash

[MFelix · Accepted solution]

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

[Anonymous]

Hi @MFelix ,

9 relationships for 6 tables is working fine in my POC. Let me implement in my original Dashboard.

Regards,

Prakash

[Anonymous]

Hey, what should I do if Sheet1, Sheet2 and Sheet3 are also related in this case.

[Anonymous]

Hi,

I have details in 3 grid whose source is 3 different tables. I have 3 common columns among them which i am going to keep in slicers at the right side.

Now i created distinct 3 tables for slicers.

If i select any one value from any one slicer then it should get affected to all the 3 tables present at the left hand side.

Any suggetions please...

Thanks 

Prakash 

[MFelix]

Hi @Anonymous ,

Check the file attach with the model setup.

However since your model has very low number of data the filtering will only return one line for each of the slicers but with more data this should work fine.

Looking at the data it seem that the information for Filter columns is Name, Gender and probalby age so you can make a single table with all 3 tables and relate with the other 3 by Filter 1 column (if values are unique) then make the filtering based on that table.

[MFelix]

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.