Need Help with relationship and date drill down issues

[dbrandone]

I have a weird report that I was asked to make and it uses not ideal data, but I have put it together but one measure will not interact with a date slicer that I have on the page. I only have 3 tables. Date table, a Health Check table(pulled date from sharepoint list from form) and a badge access table(tracks when employees come into work). The issue from the beginning is that the data comes from different sources and the unique identifiers are not ideal. Both sources have the Office 365 email for each employee so I am forced to use that to connect the two tables for relationship and then I have one table connected to the date table.

I have confirmed that all tables have the date formats correct so they should talk to each other, but when I select a week ending, all measures except the table not connected to the date table directly will not update and instead keeps all time frame data. Any idea why that table would not be affected by the slicer?

This is not an ideal setup for this report, but unfortunately the data sources don't afford me the option to have better identifiers to have solid relationships. 

[v-henryk-mstf]

Hi @dbrandone ,

Based on your description, first I suggest you read the following documents on how to create relationships for complex tables.

related document link:

Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Docs

If there are still problems, please provide relevant data model and demand information, I will answer you as soon as possible. Looking forward to your reply.

Best Regards,
Henry

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

[dbrandone]

@v-henryk-mstf 

Below is a picture of the relationship setup.

The Date from the Date table is connected to the Date in BadgeAccessData. Date intelligence between these two work fine. When I try to use date intelligence using the same filters, data from Health Check does not change. The data sources used in this makeshift report are not ideal and there are no unique identifiers in either of the two data source tables. They are connected via Office365 username. This is why I needed to use many to many to get these to work the best possible so far. If I add a relationship from Health Check to the Date table, I get the inactive message since BadgeAccess is already connected, but I am not getting the results as if they were connected.