How do I link two data fields that aren't foreign keys to synchronize filtering?

[Jaxidian]

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?

[Sean]

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!

[Jaxidian]

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 · Accepted solution]

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.

[Jaxidian]

I was afraid that was the answer but I think you're right. Thanks!