Date Slicer only showing Holidays when range changed

[feelie75]

I have a table from a query:

select employeeId, hours, intime from DW.dbo.payrollstuff

I have a Date Table defined as:

DateTable = CALENDAR(DATE(2023,01,01),date(2024,01,01))

I have a 1->many directional relationship between the DateTable to the payroll table on the 'intime' field.

I create a Table visual and pick all 3 columns. Everything shows up fine, lots of rows. I create a Slicer using the DateTable date field and...everything is fine! Default range of 01/01/2023 - 01/01/2024. All payroll rows are showing up in the table.

Now, I change the date slicer to 01/02/2023, and all of a sudden, my payroll table shows ONLY entries for 1/2/2023, 1/16/2023, 5/29/2023, 7/4/2023, and 9/4/2023. Those are all holidays!

I change the date slicer back to 01/01/2023, and still my table shows ONLY rows with those holiday dates.

When I click on the "Clear selections" icon on the date slicer, it resets the date range, and now my payroll table shows ALL rows again. 

Why would changing the date slicer from 1/1/2023 to 1/2/2023 cause a weird holiday filter to happen? And why wouldn't it revert when I changed the slicer back to 1/1/2023? Why does it only fix itself when I press the Clear Selections button to reset the date slicer to it's defaults?

I could NOT duplicate this using a payroll table created via DAX. It only happens when I'm using SQL to query the database. I'm doing Import table (not direct query). I can't attach a pbix since the query is internal of course.

Any ideas or suggestions? I've tried to simplify this as much as I can. I even started a new power BI report from scratch, and it still happens as described above. Thanks for any ideas!

(p.s. the reason I'm using a DateTable (instead of just filtering by intime) is because this project is much more complicated than what I've presented here. I have another query to another table, and I need the date slicer to be applied to both tables, so I needed a common DateTable that has a relationship to both SQL tables; I didn't include that here because I wanted to present the simplest case that still demonstrates the odd behavior.)

P.P.S. - an idea occured to me. All the holidays have a TIME component of 00:00:00 as well. other entries like vacation and sick times have time components like 08:00:00 or 13:30:00. By applying ANY date filter, is that causing it not to match properly? I thought the date filter was "BETWEEN" the start and stop date? I tried changing the DateTable date format to just "Date", not date/time, and did the same for the payroll table, and made sure the format on both was mm/dd/yyyy, but the error still happens, but maybe it has something to do with the 00:00:00 on those dates? I confirm those are the ONLY dates with 00:00:00 in the data.

[feelie75]

Yep, that was the secret sauce, gmsamborn! I just changed my SQL query to: cast(intime as Date), and then it was able to match.

I didn't know that a relationship between two tables ignored the "Formatting" when I changed it to Date. That apparently ONLY changes the formatting, but the data itself still remained date/time, so the join was only happening on the 00:00:00 payroll entries.

Changing the query to cast the value as date forced ALL entries to have 00:00:00 as their time component, and thus the relationship join now works!

Or changing the type in the column to Date as you recommended would also work. Thanks for leading me down and confirming that. Cheers!

[gmsamborn]

Glad that helped.  (I've seen that more than a couple of times.)

[gmsamborn · Accepted solution]

Hi @feelie75 

I was just reading your last paragraph and you are mentioning Datetime columns.  If the time portion is required, you'll have to make a copy of that column and change the copy to a type of Date to use in the relationship.

The reason for this is the relationship to the date table would only match columns with 00:00:00 since they would match the date column in the date table.  (Columns with a time portion other than 00:00:00 wouldn't match in the relationship.)

I hope this makes sense.