Connect 3 tables to a date table to filter a report

[jjasper]

Hello, 

I have 3 tables that do not relate to each other all. The only column that is the same in the tables is the date column. I am working a report that has many pages of information and one main dashboard. I would like to be able to have one slicer on the main dashboard that will filter all the report (3 tables) to whatever they select. I am unsure of how to go about doing this. 

From my google searching I know I need a date table that the 3 tables link up to. When I do that everything breaks and doesn't filter correctly. I tried creating this and will show each table and their date fields. When I went to create the relationships it created the relationship but the filters never worked. I even did one table to see if I could figure it out. 

Any help would be greatly appriacted. I do like being given links to follow so I can understand but please don't send me down a rabbit hole. 

Here is my date table that I created:

Date column in table 1:

Date column in table 2:

Date column in table 3:

TIA

[v-janeyg-msft · Accepted solution]

Hi, @jjasper 

It’s my pleasure to answer for you.

According to your description,I think the reason for the error is that the date columns of your three tables have specific time, which leads to no corresponding value after establishing the relationship.You can create columns to format date like mm/dd/yyyy,then create relationship.

Like this:

Columnhan = FORMAT(HandHygiene[Date2],"mm/dd/yyyy")

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[camargos88]

@jjasper ,

How did you create the measures, if any ? Are you refering to this new table ?

Also, it's possible to share this pbix ?

[jjasper]

No measures have been created and yes I am refering to the new table. https://rbjschlegel-my.sharepoint.com/:u:/g/personal/jjasper_rbjschlegel_onmicrosoft_com/EURc6Ew7pg9... 

[camargos88]

@jjasper ,

What is the wrong part when you use this date table ?

Do you get wrong calculation ?

[jjasper]

No I get nothing. When I create the relationship everything is fine I get no errors. But when I go to add the filter to the main page (Dashboard) and select a date (November) all my data cards come up with blank. But when I add a slicer for each table individaully the data filters properly so that is where I am thinking there is an error in my date table or in the relationships with I create them. 

The only thing that comes up from googling is that you can't force a relationship to be created. But I have created many relationships in other reports and never have an issue. First time trying to make a relationships for 3 tables usings a date table. 

This is using the date table and I have the relationships created with the date table. 

[camargos88]

@jjasper ,

How did you create you date table ? Does it include all the dates from the other 3 tables ?

[jjasper]

I followed this tutorial: https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

And I hope it does and I have set from the beginning of the year until the end of the year. Two of the tables only started populating recently and the other has been going since the start of the pandemic.