Forum Discussion
How do I link two data fields that aren't foreign keys to synchronize filtering?
Let's say I have two tables that look like this:
Sales:
| Id | LocationId | Amount($) | DateOfTransaction | YearNumber | MonthNumber | DayNumber |
| 1 | 42 | 12345.67 | 2012-10-22 | 2012 | 10 | 22 |
| 2 | 42 | 23456 | 2012-11-23 | 2012 | 11 | 23 |
| 3 | 45 | 987.12 | 2012-10-22 | 2012 | 10 | 22 |
| 4 | 45 | 500 | 2012-11-23 | 2012 | 11 | 23 |
Projections:
| Id | LocationId | Amount($) | DateOfTransaction | YearNumber | MonthNumber | DayNumber |
| 1 | 42 | 10000 | 2012-10-22 | 2012 | 10 | 22 |
| 2 | 42 | 20000 | 2012-11-23 | 2012 | 11 | 23 |
| 3 | 45 | 15000 | 2012-10-22 | 2012 | 10 | 22 |
| 4 | 45 | 1000 | 2012-11-23 | 2012 | 11 | 23 |
I'd like to have two bar charts such that the numbers are initially aggregated by Location ID, which is fine. But when I drill into that Location, I want to then aggregate by Year-Month (I have a computed column that shows "2012-10" and "2012-11") then next drill-down level into the Day. I'd also like the filtering on one visualization to drive the filtering on the other visualization. This is fine for Location ID but this is problematic when I'm in a drilled-down view aggregated by Month and Day. When I click on the Month in one chart, it doesn't filter by month in the other one since these Month values are in two different tables.
How can I get these to drive one another?
- You should make a separate date table that you can link both fact tables to. Then use the year-month field from the date table for the axis in your two charts.
4 Replies
SeanCommunity Champion
When you enable drill down on one chart all interactions with other charts are disabled!
Why don't you plot both Actual and Projected in the same bar chart? (At least I think that's what you are trying to do)
and you can drill down and see both amounts in the same chart
Good Luck! :smileyhappy:
Hi Sean,
I can't put them in the same chart because the dates are in different tables. I would reall love to be able to do that but it doesn't seem to be an option given how the data is structured.
- dkay84_PowerBI
Microsoft Employee
You should make a separate date table that you can link both fact tables to. Then use the year-month field from the date table for the axis in your two charts.
