Link Dates from two tables

[nairisha]

Hi,

I have two tables in my database, one containing the traffic details in a shop and another containing the transaction. Each table records the datetime when someone entered the shop and purchase something. I can separately create table showing traffic and purchase but i want to compare it in a single x axis with date or time as a common dinominator in a clustered column chart.

I have failed to achieve the desired result. Can you help me with this.

Thanks in advance.

[ankitpatira]

@nairisha Consider using Combo chart so Line and Clustered Column Chart to achieve what you're after. I am not aware of any visual which will give you multiple x-axis. Also consider using Tornado custom visual which may come closer to what you want to achieve.

[nairisha]

@ankitpatira I don't want multiple x-axis. What I want to achieve is, suppose I have two tables 

What I would like to achieve is a X-axis of may be date and Y-axis having both value 1 and value 2 on a clustered column chart.

[Eric_Zhang]

---

@nairisha wrote:

@ankitpatira I don't want multiple x-axis. What I want to achieve is, suppose I have two tables 

What I would like to achieve is a X-axis of may be date and Y-axis having both value 1 and value 2 on a clustered column chart.

---

@nairisha

For this kind of source,  you may follow below steps.

1. In the query editor, add a custom column for each source (col="traffic",col="transaction")
2. In the query editor, append queries to append those two tables.
3. In the report page, use the date as X-Axis, custom column as Legend and the value as Values, then you would get something like

[nairisha]

@Eric_Zhang your chart resembles to the one i am looking for however, im not clear about the steps. can you ellaborate more?

Thanks,

Nairisha

[Eric_Zhang]

@nairisha

Did you try to follow the steps? May I know which step are you stuck by?

[nairisha]

@Eric_Zhang i did but while creating the custom column, i did not understand which column to insert and why i need to do it.

[Eric_Zhang]

@nairisha

Add custom column valuing "tracffic "to traffic table and "transaction" to transaction. In latter steps those two table would merge into one and the newly added column is used as a legend in the visual.

[nairisha]

@Eric_Zhang In this case which column should i insert?

[Eric_Zhang]

@nairisha

My approach is as below.

In "Add Custom Column", new a column named "newColumn" and formula equals ="traffic".

[ankitpatira]

@nairisha In powerbi desktop, go to query editor (Edit Queries), on Home ribbon, under Combine tab, click Merge Queries.Select date column from both the tables and appropriate join type. Result will give you combine date with values from both tables that you can plot on x and y axis.