Forum Discussion
Advocate VHow do I create one slicer that manipulates two tables?
My report has a stacked column chart which has data from two different columns: "Old Clients[Amount]" & "New Clients[Amount]"). Currently I have two slicers: one for "Old Clients[Id]" and one for "New Clients[Id]". Both "Id" columns are identical (don't ask me to explain why, but it makes sense).
Naturally, I want just one slicer to filter both tables, instead of having two identical looking slicers. I tried to create a relationship between the "Id" columns, but I get the error message "You can't create a relationship between these two columns because one of the columns must have unique values".
Where do I go from here?
EDIT
I ended up having to restructure my data in the Query Editor. I pulled data from several tables and created one single table out of them (via Append, not optimal but worked in this case). Then I created a custom column to calculate an ID which in turn I ran through DISTINCT() to a new table [AppendReportSlicer]. Then I created a relationship between these two tables and used the column from [AppendReportSlicer] as a slicer filter.
Hi carlosDash,
A normal relationship in Power BI treats one table as a Parent table while the other is the Child table (or 1 to many).
The parent table should only have 1 record per ID. You can probably create a table using DAX or the Query Editor to make sure you have unique ID's in this table. Once you have this, you can join it to multiple detail/child tables where there can be as many instances of each ID as you like. This parent table is now also your filter table for all connected child tables. So if you use the parent table as your Slicer, any visual you build over each of the child tables will react accordingly.
Does that help?
2 Replies
- Phil_Seamark
Microsoft Employee
Hi carlosDash,
A normal relationship in Power BI treats one table as a Parent table while the other is the Child table (or 1 to many).
The parent table should only have 1 record per ID. You can probably create a table using DAX or the Query Editor to make sure you have unique ID's in this table. Once you have this, you can join it to multiple detail/child tables where there can be as many instances of each ID as you like. This parent table is now also your filter table for all connected child tables. So if you use the parent table as your Slicer, any visual you build over each of the child tables will react accordingly.
Does that help?
carlosDashYes, I think I got it now. If I understood correctly, I need to create a new table(column) which has been created by a "Distinct"-type of function selecting all the IDs, then link that to both of the tables I want to filter and then use the new table in the slicer.