Date Filtering Returning No Data

[NeilL]

Good Afternoon,
I feel as though I'm losing my mind. I can't get the simplest of slicers to work. I'm connected to Dynamics365 and have created a date table within PowerBI.
I have two tables and between them exists a single 1:N connection between Dates[Date] <-> Opportunities[kbdg_startdate]. 

There can/will be multiple opportunities that have the same start date, however, in my sample data there exists only one currently as seen in the images below.

I throw a table on a page and data shows up until I add a slicer.

My Dates table has been marked as a Date Table and validated successfully.

Can someone help?

I used to think I was geting the hang of PowerBI... today it is proving me wrong.

[MartynRamsden]

Hi @NeilL 

Make sure that your Opportunities[kbdg_startdate] column is formatted as Date and not text.

Also, I'd recommend that you change the cross filter direction of your relationship to 'Single'.

Bidirectional relationships can cause ambiguity in your model and can result in incorrect calculations.

Best regards,
Martyn

If I answered your question, please help others by accepting it as a solution.

[NeilL]

@MartynRamsden  - Thanks for the quick reply and suggestions. I double-checked and have provided screenshots indicating that fields are of the same type (datetime) and cross filter direction is single, yet the problem persists.

I'm baffled.

Ever encountered anything like this before?

[MartynRamsden]

@NeilL 

Haven't seen anything like this before.

Any chance you could share your pbix so I can try to figure out what's going on?

Best regards,
Martyn

If I answered your question, please help others by accepting it as a solution.

[MartynRamsden · Accepted solution]

Hi @NeilL 

Thanks for sharing your pbix via email. I've taken a look and have found the issue.

Your Opportunities[kbdg_startdate] column contains time values which don't map to a value in your Date column.

E.g. the value 01/03/2020 06:00:00 wouldn't map to 01/03/2020 00:00:00 in your Date table.

For performance reasons, it's usually best to separate Date and Time values into separate columns before the data is imported in Power BI (e.g. in a SQL view).

If this isn't possible or you're working with a small dataset, you can use the following DAX expression to create a calculated column in your Opportunities table and then use this column to create the relationship to the date table:

StartDate = DATE( YEAR( opportunities[kbdg_startdate] ), MONTH( opportunities[kbdg_startdate] ), DAY( opportunities[kbdg_startdate] ) )

Best regards,
Martyn

[NeilL]

I noticed the time piece just a few minutes prior to your response. I went into Dynamics and changed a setting then voila! Filtering is working.

Thank you for your time this afternoon! It is much appreciated.

[NeilL]

I noticed something odd. The data coming from Dynamics have times of 5:00:00 AM and 6:00:00 AM. I didn't enter those time stamps. The field is Date Only within Dyanamics... which leads me to believe that the issue is on that end as opposed to Power BI.
*Shaking my head in disbelief*.