Adding date calendar to allow filter/slicer to be applied to 1 visual using 2 tables

[Daniel_B]

Howdy

I have 2 tables each with a column "Date" which are formatted exactly the same but may have different values in them depending on when the row data was collected

From each table, I have created the necessary measures to get the calculations I want but as my date columns are in the 2 tables I can only filter one set of data

I've tried linking the relationship between the date fields but that didn't work (probably because I'm not proficient in that linkages) and I've tried the basics of simply applying it to the page as opposed to the visualisation but still only 1 works

If it is related to the relationship, could someone help me understand which link I should use and why or if I need to create a new calendar table (which I know how to do) what would be the next steps to get a single date filter for all of my measures?

Thanks again

[Tad17 · Accepted solution]

Go to "Edit Queries"

Right click on each query and select reference

for each, highlight your "date" column and then select "Remove Other Columns"

Then append one query to the other

Then select remove duplicates

Then right click on the query reference that was appended to the other one and disbale "Enable load"

Then create a relationship between the new query and your two original queries.

This will give you a table that just has one instance of all of the dates contained in the other two tables and it will update whenever your queries update.

If you have problems getting them to relate make sure the column titles are identical (you can do this in the Query editor) 

[judspud]

Hi @Daniel_B 

I would recommend creating a date table within your data model. This will allow you to filter both of your tables by one field. It will also mean any additional tables you add can also be filtered by the date.

Once you have created the Date table in your model, create the relationships to the other tables and then use the Date field from your new Date table in the slicer to filter.

Hope this helps

Thanks,

George