Two tables, one date dimension

[m4xpower]

Hi all,

I have two tables each containing a date column and several other columns. Each row describes an event, that happend on the date in the date column.

The tables are completely independent and have nothing in common except for the date column.

Now I want do display measures from both tables over the same timeline in one chart. This works if both date columns are named the same.

However, I want the user to be able to filter both tables independently. But whenever I apply a filter on one table, I lose all rows with the corresponding dates in the other one, too.

I managed to make the measures "immune" to filtering the unrelated table, but I just cannot set all filters I need because of the date restriction.

Kind regards

[BjoernWollny]

Hi,

use the date field once in each table with the name "date" and once with an individual name like date_table1.

So you have a copy of your date column but with another name.

This should result in a data model, where both tables are matched via date field => you can filter for a date and see corresponding data sets of both.

But you can also filter for date_table1 => this will show you all entries from table1 + entries from table2 where date is the same.

another way would be to keep different field names and use Set Analysis and a variable for the current dates from the other field. So in a Variable "vTable2Dates" you are concatenating (concat) the dates of Date field from table 2 and for values from Table1 you can use:

sum({<Table1_Date={'$(vTable2Dates)'}>} Table1_Value)

If you would like, upload some data and I can give a concrete example.

[m4xpower]

I attached some  data with the two tables and a common date field like in your first solution.

But whenever I filter one table for a field, all data from the other table disappears because they have no dates in common.

I'm not sure I understand your second approach. I would be glad if you could show me with my data.

[BjoernWollny]

Hi,

I checked the app, and probably the issue relies withing the field itself, since it contains the time.

I think you can easily fix it by using:

date("Actual Route Update DateTime") as eventDate;

And for sure applying the same on the other table.

[m4xpower]

Yes, I tried a similar approach by clustering the eventDate into calendar weeks. This works but I was hoping that there is a more elegant solution.

[BjoernWollny · Accepted solution]

Probably not, cause without any formula for date() or new fields, you have no matches in both tables related to the data. 2/2/2020 11:30 is not the same as 2/2/2020 10:25

So just create one field for date in each table, that uses only date without time. I think this is the most elegant solution 🙂

best regards