Graph data from 2 tables with relationships to 2 lookup tables

[madeline]

I have 2 tables that are formatted the same way. The first table shows the weekly average while the second table shows data for the current week. 

Example data from table 1 (weekly average) and table 2 (current week):

Weekly Average	-	-	-	Current Week	-	-	-
User	Day	Hour	EventCounts	User	Day	Hour	EventCounts
Mary	Monday	10:00	2	Mary	Monday	10:00	1
Mary	Monday	11:00	1	John	Monday	10:00	5
Frank	Monday	10:00	1	Frank	Monday	10:00	1
John	Monday	11:00	3	Frank	Monday	11:00	2
Jane	Monday	11:00	4		Tuesday
Frank	Monday	11:00	2		etc.
	Tuesday
	etc.

 I want to be able to graph the event counts from both of these tables where the Day and Hour are on the x-axis and the users are separated through a legend. (Sorry if that seems confusing, but I'll add pictures to show what I mean).

In this picture, I was able to graph both data from Average Table and Current Table by Day/Hour, but I can't see the individual users.

In the picture below, I was able to graph the users of the Current Table for each day/hour, but not the users/data from the Average Table. I want to combine the concept above with the concept below so I can compare the average user events with the current user events.

Here's my thought process:

- create relationships for both the Average Table and the Current Table each to 2 lookup tables: DayofWeek (Mon, Tues, etc.) and ListofUsers (Mary, John, Frank, Jane), so there are 4 relationships in total.

- graph using Day, Hour (from Current Table) as Axis, using User (from ListOfUsers) as Legend, using EventCounts (from Average Table), EventCounts (from Current Table) as Value

I'm able to successfully activate 3 of the relationships, but it won't let me activate the 4th because it "would introduce ambiguity between tables DayofWeek and ListofUsers." I've tried filtering through both single and both directions, but to no avail (single direction for DayofWeek breaks the graph). 

Is it possible to create a graph like this or am I way overcomplicating things?

[v-chuncz-msft · Accepted solution]

@madeline,

You may add a column to distinguish data from both tables, then use Append Queries (Table.Combine) in Query Editor or UNION Function (DAX) to create a new table.

[madeline]

Hi Sam,

Thanks for the reply. I've combined the two tables into one so that it looks like this:

UserAvg	DayAvg	HourAvg	EventCountsAvg	UserCur	DayCur	HourCur	EventCountsCur
Mary	Monday	10:00	2	Mary	Monday	10:00	1
				John	Monday	10:00	5
Frank	Monday	10:00	1	Frank	Monday	10:00	1
Mary	Monday	11:00	1
John	Monday	11:00	3
Jane	Monday	11:00	4
Frank	Monday	11:00	2	Frank	Monday	11:00	2

However, I still can't get individual bars for each user (the graph I get looks like the first result from above). I can't drag anything into the Legend category. 

[v-chuncz-msft]

@madeline,

Not the Merge Queries.