One slicer for two tables

[Anonymous]

Hi,

I have the two tables below and would like to create three slicers, Region,Year,Month;

But i do not have any way of creating a true relashionship between the two tables is this possible to do via dax?

Region	Year	Month	Target
UK	2018	Jan	212
EU	2017	Feb	122
USA	2016	March	12

Region	Year	Month	Rev
UK	2018	Jan	23322323
EU	2017	Feb	32323
USA	2016	March	232323

Thanks

Peter

[Greg_Deckler]

I would recommend creating a Region table, Year table and Month table. Relate these tables to both of your tables. Use the common tables as the slicers.

[Anonymous]

Sounds like a plan!

Would it best to ref the tables if any new values every get updated? can you even do that?

And if you can how? 🙂

Thanks

Peter

[Greg_Deckler · Accepted solution]

Here's what you generally do. Just copy and paste your query for one of your tables. Remove all of the other columns other than the one that you are interested in, let's say "Year". Remove duplicates. Save as your Year table. Now, if a new year is added in the data, you will automatically get it in your Year table.

If you have some values in one table and some in another, then what you want to do is do as above and then repeat for your other table and then create an Append query to append them together and remove duplicate values. Now you have a dimension table that has all of the possible values in both of your tables.

[Anonymous]

Thank you that worked

[Greg_Deckler]

Awesome, and we made it through the entire thread without any reference to girls or cups or anything...