Common Filter for Different Data Source or Table

[samihuq]

Dear Team,

I have got Two Tables Imported from Excel in PowerBi and they are not linked. Both of the Table has date column and calculated column that derives Year and Month Field.

Now when i select Year or Month as filter or Slicer from a particular Table, only that table filters. Now is there any way we can connect both the table using this single Year- Month Slicer or Filter? Or is there any other work around other then creating relationship?

Thanks,
Sami

[jclark88 · Accepted solution]

Unless there is a specific reason to avoid relationships (I can't think of one!) you'll need to use them..

A date dimension is commonly used for this.. You'll want a table with all the dates in a range your interested in, and you can have columns which have the different ways you may want to cut the data:

DateId	Date	Year	Month	Day	Year Month
1	01/01/2015	2015	January	Thursday	Jan-15
2	02/01/2015	2015	January	Friday	Jan-15
3	03/01/2015	2015	January	Saturday	Jan-15
4	04/01/2015	2015	January	Sunday	Jan-15
5	05/01/2015	2015	January	Monday	Jan-15

You can either create a fixed table in excel, or if you want to get fancier you can generate is dynamically.

Join this table to your two excel tables and insert the DateId into them - then you can create the relationships between them in the relationships editor using the DateId key. The advantage of this approach is that you have a common, continuos set of dates and ways to slice the data

[megjain9]

Is it possible in Power BI service as well?

[samihuq]

Thanks man for your time, will try to incorporate that. 

Cheers,
Sami