Cannot match date to date table

[AlmostDev]

I'm trying to make reports from our ticketing system. Most require displaying ticket count per month with some added info or filtering. I created a calendarauto table with relation to the ticket table in DB (directquery) and added a relationship. Problem is when using these together, the tickets' timestamps are matches with the whole date and time to rows in calendar so at the moment I get only a single match for a ticket that happens to have time 12:00:00 AM. I tried changing data types for the fields from datetime to date but that only changed displayed values. I have a separate GroupBy table that works to some extent but it breaks context and relations and probably something else I don't even know. 

Can I somehow enter custom aggregation or compare rules?
relationshipscreated visualization. Left is count of ticket Ids, calendar date, calendar month year part and ticket creatime_time., middle is raw calendar, right is raw ticket create_time

[onurbmiguel_ · Accepted solution]

Hello AlmostDev

Did you need to use the hours? 

If not, go to power query and change to date the two columns. 

close and applly 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! ;-
Best Regards
BC

[AlmostDev]

I thought this wouldn't work because directquery doesn't allow data type changes but did for this? I just need the dates for this so this is fine. In order to still have times in case I need them (eg for SLA) I made a duplicate column which I changed to Date.

Some additional data type and format changes and now I have what I need

[amitchandak]

@AlmostDev , Create a new column

Create_date = Datevalue([Craeted_time])

join this with Date table

[AlmostDev]

I would prefer this but I get "OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression." when using the Datevalue.