Can I combine data from separate data sources into a single Date Hierarchy visualization?

[joelmerriman]

I would like to be able to utilize two or more tables in a single visualization based on the Date Hiearchy/Drilldown.  One table would provide the number of unique visitors to a page for each day.  Another would provide the number of unique visitors to a page for each month.  Another for each Quarter.  etc.

This is important for the data I'm dealing with, because summing the daily visitor numbers for each day when switching to a month view would cause severe overcounting of the number of unique visitors.  A single visitor who came to the page every day in a month would be counted as ~30 visitors, rather than one unique user for that time period.

Obviously I could settle for Average instead of Sum, but preferably I need a way to make it so that when a user drills into, or out of a level in the Date Hiearchy, the visualization will change which table it actually pulls the data from.  The format in each table would be identical.  Each would just contain pre-aggregated daily numbers, or monthly, or quarterly, etc.

Is there any way to accomplish this?

[Phil_Seamark]

This can be done by both the Query Editor and DAX.   If you can do it in the Query Editor this will result in faster data models.

You could have a table with 4 columsn.  

1. The 1st is the date (1 row per day)
2. then the visitors per day
3. for the first day of the month only, the unique visitors for the month (all other rows blank)
4. for the first day of the Quarter, the unique visitors for the Quarter (all other rows blank)

This will avoid over counting.

I'm sure there are other approaches, but I thought I'd start with one suggestion 🙂

[joelmerriman]

Interesting, but this wouldn't provide what I need without some serious rework.  The data for the dates are in completely separate tables with at least 1 column for dimension data such as the Page viewed, or the Country a visitor is from, and several columns of measures for visits, page views, time on site, etc. 

I basically have data like the below.  The first column in each is the time period.  The second and third columns are dimensions which are being measured, in this case the device type and the operating system used to view a web page.  The remaining columns are measures about that combination of device type and os, but aggregated based on the time period in the selection.

Your idea could work if I can pull out data from each table and place it in a new one I suppose, but since this would be taking place on several measures (not just visitors), and several reports, it would get exponentially more time consuming to create.  Plus, I'm really new at this and have to learn DAX.  🙂

Time Period	Device Type	Platforms	Sessions	Page Views	Single Page View Sessions	Entry Page Sessions	Avg View Time (Sec)	Avg Session Duration (Mins)	Bounce Rate	Views per Session
1/31/2017	Desktop	Windows 10	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX
1/31/2017	Mobile Phone	Windows 10	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX
2/1/2017	Desktop	Windows 10	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX
2/1/2017	Mobile Phone	Windows 10	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX

Time Period	Device Type	Platforms	Sessions	Page Views	Single Page View Sessions	Entry Page Sessions	Avg View Time (Sec)	Avg Session Duration (Mins)	Bounce Rate	Views per Session
Jan-17	Desktop	Windows 10	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX
Jan-17	Mobile Phone	Windows 10	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX
Feb-17	Desktop	Windows 10	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX
Feb-17	Mobile Phone	Windows 10	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX

Time Period	Device Type	Platforms	Sessions	Page Views	Single Page View Sessions	Entry Page Sessions	Avg View Time (Sec)	Avg Session Duration (Mins)	Bounce Rate	Views per Session
Q1 2017	Desktop	Windows 10	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX
Q1 2017	Mobile Phone	Windows 10	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX

Time Period	Device Type	Platforms	Sessions	Page Views	Single Page View Sessions	Entry Page Sessions	Avg View Time (Sec)	Avg Session Duration (Mins)	Bounce Rate	Views per Session
2017	Desktop	Windows 10	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX
2017	Mobile Phone	Windows 10	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX	XXXX