Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
Is it possible in Power BI service as well?
Thanks man for your time, will try to incorporate that.
Cheers,
Sami
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
68 |