Joining of Date/Time columns between two tables isn't producing expected output

[ronp23]

Table 1:  QuickDate table consisting of a Date/time column (called DateTime) formatted as  MM/DD/YY HH:MM:SS for last 7 days

Table 2:  VLData table holding detailed records including Date/Time column (called Date Received (EST)) formatted as MM/DD/YY HH:MM:SS

I want to join the 1 to * from Table 1 to Table 2 using the DateTime column to Date Received (EST) column. 

Data Transformation:

I need to use a scatter plot to chart the DateReceived (EST) on the X Axis and the Hours Behind on the Y Axis.  Becasue scatter charts require aggregated values on the Y Axis when using a date filed on teh X axis, I have turned each timestanmp record into an Index value (type Whole).  So I can plt the Index on the X and the Hours Behind on the Y. Here is a sample of the current excel chart that we want to replicate in Power BI.

After making the join:

1. The DateTime format icon on the DateReceived (EST) column disappears.

2. Only a single DateTime value is returned when visualizing the data in a table.  And that is even inconsistent. 

Expected Outcome

How do you get the unique records in the Quick Date table to display for each matching record in the VL Data Table?

A scatter chart (like teh excel chart above) with the Index value on the X axis and the Hours Behind in the Y axis.  Becasue there are gaps in periods of time within the VL Data Table its important the Index value is on the X axis so you can see those gaps.  

[v-rzhou-msft]

Hi @ronp23 

According to your statement, we know that create a relationship between "DATE QUICK" and "VL Data" tables by [Date Time] and [Date Received(EST)]. Both of them are in format of MM/DD/YY HH:MM:SS.

From your screenshot, we could see that only 11/14/2021 6:40:52 AM will return result. We need to know that values in both columns should be the same (11/14/2021 6:40:52 AM) or calculate will return Blank when you join two tables by this relationships. 

Please check whether other datetimes in two tables in same day have the same datetime. Or you can try to add a Date column in both tables and then calculate results by date instead of datetime.

Note Date value your create by dax will return 12:00:00 AM in Datetime format.

If this reply still couldn't help you solve your problem, please share a sample with me.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @ronp23 ,

First of all when you make a realtionship between two table the icon on stays on the one side of the relationship is a visual way for you to know wich field you should use for the visualizations and calculations.

The second part of your question, I assume that your Date table is single value for each date so you should have the dates has 01/01/2021 00:00:00 (or similar) so in this case the relationship is only being made for that specific time.

For this you need to have a line on date table for each of the date/time values taking into account that you have it in seconds you would need to have something similar to this:

01/01/2021 00:00:00

01/01/2021 00:00:01

01/01/2021 00:00:02

01/01/2021 00:00:03

....

31/12/2021 23:59:55

31/12/2021 23:59:56

31/12/2021 23:59:57

31/12/2021 23:59:58

31/12/2021 23:59:59

This will give a very large table I believe that the best option is to have this split into time a date table with relationship to your fact table.

[ronp23]

Thank you @MFelix - 

I appreciate the context around the icon disappearing during the join.   

The DateTime table I created is just as you suggest.  I created the table as follows:

1. I created a TimeTable

TimeTable =

VAR HourTable = SELECTCOLUMNS(GENERATESERIES((0), (23)), "Hour", [Value])

VAR MinuteTable = SELECTCOLUMNS(GENERATESERIES((0), (59)), "Minute", [Value])

VAR SecondsTable = SELECTCOLUMNS(GENERATESERIES((0), (59)),"Second", [Value])

RETURN

ADDCOLUMNS(

CROSSJOIN(HourTable, MinuteTable, SecondsTable),

/*Format this as a Time data type and then format as 01:30:55 PM */

"Time", TIME([Hour], [Minute], [Second])

)

2. The added the full Date table (DATE QUICK) by doing the following CrossJoin:

DATE QUICK = ADDCOLUMNS (

CROSSJOIN(

CALENDAR(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())-5),TODAY()),

TimeTable

),

"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),

"DateTime", [Date] + TimeTable[Time]

)

Here is a small sample of the result becasue it is 518,400 rows:

I feel like I am on the right track but not getting desired output.  

[MFelix]

Hi @ronp23 ,

You need to make the relationship between the DATE QUICK  and the VL Data.

In the example below I have small model:

Has you can see in the table above I can see all the values (be aware that in the table visualization you need to select show items with no data) but if you place it on a chart the values will appear with the blank values:

Can you please share a mockup data or sample of your PBIX file and an example of the final result. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[ronp23]

Thank you @v-rzhou-msft

I provided my logic for creating a DAATEQUICK Table which has a reocrd for every second for the past 5 days.  I did that because teh Fact table also provides data in teh MM/DD/YYY HH:MM:SS AM/PM format. 

Here is sample data from the fact table.  The Date Received (EST) column is being linked with teh DateTime column from the DATEQUICK table. 

Additional comments are appreciated to help solve this issue.