Power BI date and time display issue

[Dunner2020]

Hi there,

I have three tables: date, time, and customer information. Now I want to display information on table visulation in such a wat it shows date from date table, time from time table, no of customers from customer information table. When I drag the date and time columns into table visulation one of them stopped displaying information. I checked the relationship but could not find any issue. I have provided the link of sample file (https://1drv.ms/u/s!AucycxZHFe9TjS6G96JVtgslQLBX?e=aCmzB3 ). Could anyone guide me where am I making the mistake?

[amitchandak]

@Dunner2020 , I have not visited your link yet. If you have date-time.

You need to columns out from it Date and Time

Date = [datetime].Date // Join with date tbale

Time =[datetime].time  //join with time table

[Dunner2020]

I have separate date and time columns in customer information table which is linked with date and time tables

[mahoneypat]

Your Time(NZST) column used in your relationship is in DateTime format.  It still has the Date component.  You need to change that in the query editor so that it matches with the Time table values (which have 12/31/1899 default date component: all times have a date component, even when not visible so they have to match).

Regards,

Pat

[Dunner2020]

Yes I have changed the time format, but same behaviour.

[mahoneypat · Accepted solution]

Did you make a new Time column in the query?  Changing the Data Type of the column in Data View is not enough.  To demonstrate, I made a new DAX time column from your Time (NZST) column with this formula.

Just Time =
TIME ( HOUR ( 'Customer Information'[Time (NZST)] ), MINUTE ( 'Customer Information'[Time (NZST)] ), SECOND ( 'Customer Information'[Time (NZST)] ) )

If I make the relationship using this time column instead, your visual works as expected.  You need to either change it to a true Time column in query or add a column like the one above.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Dunner2020]

Thanks @mahoneypat , You were right. I was making changes in the data view. When I made changes in the query, it worked. Thanks a new learning for me.

[Dunner2020]

@mahoneypat , Thanks for your reply. I have changed the time formats into time format in both table. But did not fix the issue. Still does not display time.

[Dunner2020]

I do not have a date-time column.