merging 2 tables in one chart using the date

[Anonymous]

Hi,

This is my first post here. A couple of weeks ago I started to use Power BI as our visualization tool. I'm so excited to see how easy is to merge all the data sources that we have into a single reporting tool.

I have just found my first stumbling block though. It's about dates!

I am trying to build a bar chart that shows a sort of funnel from the moment user comes to the site until he/she activates the software. The chart configuration would be as follows:

Axis: date

Values: 

  1. Sessions. Taken from Google Analytics in a table with date and sessions

  2. Registrations. Taken from our database, table "Users". Each user_id has a join_date so I'm just doing a Distinct Count here

  3. Software activations. Taken from our database. Same table as Registrations and similar aggregation formula.

I first created the chart with 2 and 3 grouped by month. No issues there. However, when adding "Sessions" (1) to the chart it adds a 3rd bar with the same value for all months (that value is the sum of all sessions). 

I know there's something I need to do with the date fields, probably the format but haven't been able to figure out.

This is what I tried:

1. I created a date table by using the values of the table for Registrations and SW activations with this formula:

Datetable = CALENDAR(MIN('users'[date_joined]),MAX('users'[date_joined]))

I then marked it as "Date Table"

2. I tried to build relationships between the Google Analytics table and the users table

3. I also tried to give the same date format to all date fields (I tried with Date and Date/Time)

Any ideas?

Thanks a lot!

PS: something similar happened when trying to create a slicer that controls different charts with different date fields from different tables. I have the feeling that solving the issue presented here will also help me with my slicer problem 🙂

[v-chuncz-msft]

@Anonymous,

It seems that you may add relationships to the date table.

[Anonymous]

Thanks Sam!

I tried that but didn't work.

For example, in this test I have 3 tables:

- Date table

- Renewal Orders

- New Subscription Orders

Relationships as follows:

'Date table'.date-column  1:*  'Renewals Orders'.dateR-column

'Date table'.date-column  1:*  'New Subscription Orders'.dateNS-column

The date table is also marked as Date Table

And here is what happens when applying the slicer

- Without filtering the slicer: https://www.screencast.com/t/c31uWft2xa

- Seleting a range in the slicer: https://www.screencast.com/t/ISHFTBl7

Any ideas?

Thanks

[Anonymous]

Just made another test with manually entered tables and applyed same relationships. This seems to work so there must be a problem with my data. I have the feeling that it has to do with the format but I changed the date field format in all tables to the same type and still not working. 

I'll keep playing with the fields. Any ideas will be welcome 😄

[Anonymous · Accepted solution]

Finally! I managed to solve it

I think the main problem was coming from my Date Table and the timestamp format from my date fields

//following 2 steps were my fail attempts

1) This how I first created my date table:

DateTable = CALENDAR(MIN('tableA'[date_field]),MAX('tableA'[date_field]))

That seemed to have created a proper column but nothing was working after doing all corresponding relationships and changing date formats through Modelling menu

2) I used a different formula for the Datetable:

thefinaltable = CALENDAR(FIRSTDATE('tableA'[date_field]),LASTDATE('tableA'[date_field]))

However, this was giving me the following error:

A date column containing duplicate dates was specified in the call to function 'FIRSTDATE'. This is not supported.

// everything worked from here

3.1) I then created a new date field on tableA by using the following formula:

newdatecol = DATE(YEAR('tableA'[date_field]),MONTH('tableA'[date_field]),DAY('tableA'[date_field]))

3.2) Changed the format through the Modelling menu to the default 'Date' format

4.1) I tried to again create the DateTable with the same formula but using the new column from tableA:

thefinaltable = CALENDAR(FIRSTDATE('tableA'[newdatecol]),LASTDATE('tableA'[newdatecol]))

4.2) I gave the same Date format as the new date column from TableA

After that, all my slicers worked perfectly on tableA and also tableB which had similar data.