Odd behaviour with a date table (need help)

[RichHead1821]

I have a model which includes a date table, which has an active link to a date in a fact table, and inactive links to dates in the same table (and active/inactive links to other tables too). On the date in my fact table, the filtering is not working correctly, for example I can see there are dates from 01/01/2017 through to current date in the fact table, but my slicer for the date table's date filter is resulting in data dropping out of the filtered visual as I drag the end date across 2018, there seems to be a date where it just "stops working". The slicer itself is filtered to the "last 10 years" and does not include today (so that all future dates are not on the slicer), no other filters are applied to the visual displaying the fact data.

I'm at a complete loss as to why this is happening... anything obvious I may have missed?

[RichHead1821]

Here's the active relationship:

The inactive relationship is between dateTable[date] and applications[applicationsDecisionDate]

One card visual which is a distinct count of applications[applicationId], with a slicer on dateTable[date]

As soon as the slicer is moved from 01/01/2017, the count is incorrect:

Here's a snapshot of the data (showing range of dates):

I'm very confused...

[Anonymous]

Hi @RichHead1821 ,

It is strange. Based on the screenshot of the data you provided, it looks like there was more than one piece of data between 06/02/2017 and 31/12/2030. Did you add some other filters to the card visual in Filters pane after you set the start date of the slicer to 06/02/2017, and does the card visual show the correct values if you click Refresh button? And it still can display the correct count when set another start date for the slicer? By the way, what's the version of your Power BI Desktop? The latest version is 2.100.1182.0.

Best Regards

[RichHead1821]

I'm at a loss, if I filter directly on the application decision date, the filter/slicer works, something amiss in the relationship, but I've tried active and inactive relationships, measures with userelationship, there are no cross filters (and I've used "all" in the measure to be sure).

Version is 

2.100.1182.0 64-bit (December 2021)

[Anonymous]

Hi @RichHead1821 ,

It is strange... I tried to reproduce your problem based on the information you provided (example data, table relationships, slicer settings and card visual objects, etc.) but could not reproduce it: card visual was able to display the appropriate data based on the selection of the date slicer. If it is possible, could you please share a simplified pbix file(exclude sensitive data) in order to make troubleshooting and find the cause to solve it? Thank you.

Best Regards

[RichHead1821]

I'd be happy to, but am not sure how to share the PBIX file here...

[RichHead1821]

Hope this works: https://1drv.ms/u/s!AlQw9UytOyzMhKZZoExLn0e8fbzGLw?e=aHVmLz 

[Anonymous]

Hi @RichHead1821 ,

You can refer the following thread to upload it:

How to upload PBI in Community

Best Regards

[amitchandak]

@RichHead1821 ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

date should show data outside unless join is missing or crossfilter is used

you can refer examples how it has been used

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...