Relationship of Time dimension table and FACT SQL table not connecting correctly

[tkramer]

I have an issue with my Time Dimension table in my SQL reports.  I am relating "Time" in the Dimension table to the "CreateTime" in the Fact table.  When I use any of the fields from the Time dimension table, such as "Hour", there are thousands of rows considered blank (shown below).  However, there should be no rows blank, as this is my FACT table with IDs, and every row in the table has a "CreateTime". 

To further troubleshoot the issue, I copied my SQL table into an XLXS file.  No transformations were made at all on the XLXS file so both tables are identical… but the XLXS file worked.

The only transformations that were made on the SQL table was separating the "CreateDate" into two columns (adding one column with Time only, and extracting the original with Date only).  

I need to be able to run this report from the server, but the Time dimension table will not work correctly when connecting to SQL.  This is an Import, not direct query.  I am using the Time Dimension table from Radicad.

Both sides of the relationship are Data type, "Time".  Changing these columns to type Text does not seem to work for me either.  

Here is the link to the PBIX file. 

Is there a better way to separate the original column in SQL (with both date and time) into two columns?  Do I need to approach this differently because this is a SQL table? 

I would love if someone would take a shot at figuring this out.  Thank you!

[lbendlin]

I can't find a problem with your setup (apart from the fact that you don't have a Dates table)

What's the issue?

[tkramer]

You are looking at the time dimension table related to the XLXS file. That is not the issue. The issue is when I am connecting the time dimension table to the SQL table. I posted a screenshot of the two different tables above.

I am wondering if anyone has any idea why there are so many blank rows when connecting to the SQL table. 

[lbendlin]

The reason is that your SQL timestamp includes milliseconds.

See attached for a fix.

[tkramer]

Great! That makes sense.  I saw you used the TIMEVALUE funciton in DAX to create the column, but is there a way I could do this in Power Query without having milliseconds??

I don't see milliseconds in the Time column in Power Query, so I'm not sure how to remove them.  

In my actual published report, I settled for creating a DAX Time column from each Timestamp from SQL in my tables.  It works, but I am wanting to do everything in Power Query if I can for simplicity.  It seems like the DAX created columns all connect perfectly, but using the columns with Extracting/Inserting Time in Power Query do not suffice.  

Thank you Ibendlin

[lbendlin · Accepted solution]

How to remove milliseconds or round to nearest sec... - Microsoft Power BI Community