Table showing blank rows when using HH:MM:SS time format issue

[Miguel_P]

Hello,

I have a table in which I display all my relavant business data and until now I was displaying my different time-related values in simply total seconds. However, once values get too large and there are several minutes worth of seconds, it is not so easy to read so I changed my time format to HH:MM:SS (or whatever HH:NN:SS is in Power BI)

The issue I am having is that after making that change and when filtering the data using slicers, the table now shows empty rows with persistent 00:00:00 values in the columns with time values. For example, if I filter my data because i only want to see January's data then instead of the table just displaying January as it always did before, now it does what you see below. Where the dates not currently selected in the slicer are blank except for the 00:00:00 values in the time-related columns.

Date	# of orders	# of items in order	avg call length	avg time customer waited	ship date	ship type
february			00:00:00	00:00:00
february			00:00:00	00:00:00
january	3	2	00:07:19	00:03:10	jan 31	2 day
january	5	7	00:21:03	00:02:19	jan 30	normal
january	1	3	00:11:31	00:02:47	jan 29	next day
december			00:00:00	00:00:00
november			00:00:00	00:00:00
november			00:00:00	00:00:00
november			00:00:00	00:00:00

Does anyone know how to fix this or what could possibly be going on here? I appreciate any assistance. This is a really inconvenienet bug and I cannot seem to fix it on my end.

EDIT: Because my original seconds value is not a date/time format value, the way i created the HH:MM:SS value is by using the TIME function. So I have a measure that does the following calculation...

Time_Format_Fixed = TIME(0,0,[Total_Time])

Thank you ahead of time.

[calerof]

Hi @Miguel_P ,

Do you have a Calendar table?

https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/ 

Regards,

Fernando

[Miguel_P]

Hi,

Yes, i do and they are properly related by Date.

[calerof]

If you provide sample data and your mesaures would be easier to try to help.

F

[Miguel_P · Accepted solution]

Believe it or not, i just found the issue and I'm smacking my head agaisnt the wall because of it lol

While i correctly created a Date column in my data table to then make the relationship to the calendar table... i used that date column from my data table instead of the date from the calendar table.

I just switched table's date field to be the one from the calendar table and the bug is gone.

While the fix was dumb, i believe that is something Power BI should be able to handle better.

[calerof]

You can mark your own response as an answer, so people find it.

Regards,

F

[Miguel_P]

At the end of the day, you asking me if I had a calendar table is what prompted me to check my calendar table and its relationship with my data table. Which led to me realizing I was using the wrong date column in my table.

Thanks for the nudge sir haha