Filtering data sources using a single slicer for dashboard visuals

[ninos-shiba]

I have two different data sets that each have similar information but for different respective "owner groups". I want to add a single slicer on my dashboard that, if for example I select "Dataset 1", it will show information on all my visuals in regards to the owner groups in Dataset 1. Then, if I check "Dataset 2", the visuals will filter and change for the owner groups in Dataset 2. I read on the forums that it is recommended to create a different data set, which I did and it has one column called Owner groups. I then linked the "Ownergroup" column from Dataset 1 -> the new table and "Ownergroup" column from Dataset 2 -> the new table. I added the slicer but nothing happens. Dataset 1 has a column called "Ownergroup" with 5 different groups. Dataset 2 has "Ownergroup" with 6 different groups. I'd appreciate any help! Thank you!

[TomMartens]

Hey,

please excuse, but first I want to introduce some wording, I'm not sure, but I assume by referring to a dataset you actually mean a table. In the world of Power BI a dataset is considered a collection of tables.

Now I'm using the the word table instead of dataset.

I'm not sure what "similarity" actual means, but it's not possible to "switch" the content of a visual to a differnt table, no matter if we are talking about a table visual or a bar chart.

If similarity means, both tables have the same column structure, but contain data for different regions, the solution might be "simple".

Just follow these steps using Power Query:

• Create a new column in each table that explicity allows to differentiate both tables, https://docs.microsoft.com/en-us/power-bi/desktop-add-custom-column
• Append one of the tables to the other one, https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Now you can create a slicer, based on the column "Ownergroup".

Hopefully this provides some ideas how to solve your problem.

Regards,

Tom

[ninos-shiba]

I apologize about the confusion. I come from the world of R, so I am used to using "dataset" as a data table. As for the new column in each of my two data tables, what should I name it and what should be the contents underneath them? Then I just append the first table to the second one? I am a little confused. @TomMartens 

[TomMartens]

Hey,

not sure how to name the column, the approach I mentioned only makes sense if both tables contain similar content, and only differentiate in one aspect. For example table 1 contains sales date for region 1 and table 2 contains sales data for region 2. Then you can name the new column "Region" and assign the appropriate values, tablewise.

And then just append one table to the other.

Hopefully this clarifies my 1st post a little bit.

regards,

Tom

[ninos-shiba]

So if my first table has a column called "Ownergroups" and it has 5 different groups, and my second table has a column called "Ownergroups" as well but with different groups than the first table, how would I do this?

[TomMartens]

Hey,

please excuse, I do not a understand your question, appending tables in Power BI is basically the same operation as rbind in R.

If you "stack" both tables, you can filter the combined tabels by using the Ownergroup column or just use the new column to "switch" between both datasets.

Regards,

Tom 

[ninos-shiba]

Right, so since I already have the two columns in each data table, I wouldn't have to add another column in each? I could just go ahead and bind them, correct? And then use that binded table as the slicer?

[TomMartens]

Hey,

I guess you are ready to go, just "stack" the tables (Power BI: Append | R: rbind).

But without having the details about the structure of both the tables, there is still room for misunderstanding  😉

Regards,

Tom

[ninos-shiba]

So this is after I appended the two tables together and used a single slicer. However, this isn't what I desired. This slicer lists every single individual group. What I was looking for in the slicer were just two checkboxes, "Groups from Table 1" & "Groups from Table 2". So if I checked the first box, I'd get all the groups for that table and vice versa for the Table 2 groups. Does that make sense? 

[TomMartens]

Hey,

then just use the slicer that allows to differentiate between both the stacked tables.

If the ownergroups have unique value per table, i guess this is then is what you are looking for.

Regards,

Tom

[ninos-shiba]

I'm sorry, I don't understand. So the first column I have above are the table 1 groups. The second column are the table 2 groups. How could I get the slicer to display two checkboxes, "Table 1" and "Table 2" where upon selection of either or, all the groups each table has would get displayed on the visuals. I apologize if I am confusing you. 

[TomMartens]

Hey, please create a pbix that contains some sample data and represents both of the tables, upload the pbix to onedrive or dropbox and share the link.

Regards,

Tom

[ninos-shiba · Accepted solution]

Nevermind, I figured out how to do it. After creating the new appended table, I created a new column in it that had an IF statement that labeled each subgroup as either Group 1 or Group 2. Then I created a slicer based off that column. Thank you for your help.