Hide/remove default date 30-12-1899/1-1-1900 from x-axes

[Anonymous]

Hi there,

In a .pbix file I have both a stacked column chart and a clustered column chart with 'time' on the x-axes. For the stacked column chart the x-axis is made up of every 15 minutes of a day. For the clustered column chart, the x-axis is divided up in hours of a day.

The time values come from a column with data type 'time' in a Time Dimension table (coming from Azure SQL Database) and this column has no dates in it. Nevertheless, when putting the time values on the x-axis, the charts show Power BI's default date value of 30-12-1899. I do not seem to be able to remove/hide this date value from the x-axes. Additional difficulty is provided by the fact that I HAVE to use Direct Query as eventually it has to be used in Power BI Embedded.

If I change the data type of the time column in my datasource from 'time' to 'datetime', the same problem persists but with 1-1-1900 as default date.

Can anyone help me out? Would be greatly appreciated!

Regards,

Robin

[BetterCallFrank]

Hi @Anonymous

whats the data type of the column in your PowerBI model? (not in your data source) - it can be set there separately - try to set it to time.

HTH,

Frank

[Anonymous]

Hi Frank,

I have tried setting it to all kinds of combinations of data types and formats in both the query editor as well as the visualization screen, but nothing worked. In the query editor I am very limited by the Direct Query mode, but as said I cannot deviate from that.

Regards,

Robin

[v-yuezhe-msft]

Hi @Anonymous,

Would you please post details about your table structure and sample data of your tables?

I am trying to test your scenario, but I am not able to reproduce your issue.

I firstly create a table named testtime and insert some data into this table using the following T-SQL scripts.

CREATE TABLE [dbo].[testtime](
      [Id] [int] IDENTITY(1,1) NOT NULL,
      [Amount] int NOT NULL,
      [Remnants] int NOT NULL,
      [Timestamp] [time] NOT NULL)
    

INSERT INTO [testtime]
VALUES
(5000, 2500, '10:25:00'),
(8000, 4000, '14:30:00'),
(10000, 8900, '16:10:00'),
(6100, 3500, '18:00:00'),
(4000, 1000, '21:20:00'),
(11000, 4900, '23:08:00')

Secondly, I connect to the Azure SQL database using DirectQuery mode in Power BI Desktop and Power BI Desktop recognizes the timestamp column properly. Then I create a stacked column chart and clustered column chart, everything works as expected. Please check the following screenshots to get more details.

Thanks,
Lydia Zhang

[Anonymous]

Hi Lydia,

Thanks for your elaborate reply. Here is the script I use for creating the DimTime table and the stored procedure for filling the DimTime table:

/****** Create Table [dbo].[DimTime] ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[DimTime] (
	[TimeKey] int NOT NULL
	,[TimeAltKey] int NOT NULL
	,[Time30] time(0) NOT NULL
	,[Hour30] tinyint NOT NULL
	,[MinuteNumber] tinyint NOT NULL
	,[SecondNumber] tinyint NOT NULL
	,[TimeInSecond] int NOT NULL
	,[HourlyBucket] varchar(15) NOT NULL
	,[DayTimeBucketGroupKey] int NOT NULL
	,[DayTimeBucket] varchar(100) NOT NULL CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED ([TimeKey] ASC) WITH (
		PAD_INDEX = OFF
		,STATISTICS_NORECOMPUTE = OFF
		,IGNORE_DUP_KEY = OFF
		,ALLOW_ROW_LOCKS = ON
		,ALLOW_PAGE_LOCKS = ON
		) ON [PRIMARY]
	) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

/***** Create Stored procedure In Test_DW and Run SP To Fill Time Dimension with Values****/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[FillDimTime]
AS
BEGIN
	--Specify Total Number of Hours You need to fill in Time Dimension 
	DECLARE @Size INTEGER

	--iF @Size=23 THEN This will Fill values Upto 23:59 hr in Time Dimension 
	SET @Size = 23

	DECLARE @hour INTEGER
	DECLARE @minute INTEGER
	DECLARE @second INTEGER
	DECLARE @k INTEGER
	DECLARE @TimeAltKey INTEGER
	DECLARE @TimeInSeconds INTEGER
	DECLARE @Time30 VARCHAR(25)
	DECLARE @Hour30 VARCHAR(4)
	DECLARE @Minute30 VARCHAR(4)
	DECLARE @Second30 VARCHAR(4)
	DECLARE @HourBucket VARCHAR(15)
	DECLARE @HourBucketGroupKey INT
	DECLARE @DayTimeBucket VARCHAR(100)
	DECLARE @DayTimeBucketGroupKey INT

	SET @hour = 0
	SET @minute = 0
	SET @second = 0
	SET @k = 0
	SET @TimeAltKey = 0

	WHILE (@hour <= @Size)
	BEGIN
		IF (@hour < 10)
		BEGIN
			SET @Hour30 = '0' + cast(@hour AS VARCHAR(10))
		END
		ELSE
		BEGIN
			SET @Hour30 = @hour
		END

		--Create Hour Bucket Value 
		SET @HourBucket = @Hour30 + ':00' + '-' + @Hour30 + ':59'

		WHILE (@minute <= 59)
		BEGIN
			WHILE (@second <= 59)
			BEGIN
				SET @TimeAltKey = @hour * 10000 + @minute * 100 + @second
				SET @TimeInSeconds = @hour * 3600 + @minute * 60 + @second

				IF @minute < 10
				BEGIN
					SET @Minute30 = '0' + cast(@minute AS VARCHAR(10))
				END
				ELSE
				BEGIN
					SET @Minute30 = @minute
				END

				IF @second < 10
				BEGIN
					SET @Second30 = '0' + cast(@second AS VARCHAR(10))
				END
				ELSE
				BEGIN
					SET @Second30 = @second
				END

				--Concatenate values for Time30 
				SET @Time30 = @Hour30 + ':' + @Minute30 + ':' + @Second30

				--DayTimeBucketGroupKey can be used in Sorting of DayTime Bucket In proper Order 
				SELECT @DayTimeBucketGroupKey = CASE 
						WHEN (
								@TimeAltKey >= 00000
								AND @TimeAltKey <= 25959
								)
							THEN 0
						WHEN (
								@TimeAltKey >= 30000
								AND @TimeAltKey <= 65959
								)
							THEN 1
						WHEN (
								@TimeAltKey >= 70000
								AND @TimeAltKey <= 85959
								)
							THEN 2
						WHEN (
								@TimeAltKey >= 90000
								AND @TimeAltKey <= 115959
								)
							THEN 3
						WHEN (
								@TimeAltKey >= 120000
								AND @TimeAltKey <= 135959
								)
							THEN 4
						WHEN (
								@TimeAltKey >= 140000
								AND @TimeAltKey <= 155959
								)
							THEN 5
						WHEN (
								@TimeAltKey >= 50000
								AND @TimeAltKey <= 175959
								)
							THEN 6
						WHEN (
								@TimeAltKey >= 180000
								AND @TimeAltKey <= 235959
								)
							THEN 7
						WHEN (@TimeAltKey >= 240000)
							THEN 8
						END

				--print @DayTimeBucketGroupKey 
				-- DayTimeBucket Time Divided in Specific Time Zone 
				-- So Data can Be Grouped as per Bucket for Analyzing as per time of day 
				SELECT @DayTimeBucket = CASE 
						WHEN (
								@TimeAltKey >= 00000
								AND @TimeAltKey <= 25959
								)
							THEN 'Late Night (00:00 AM To 02:59 AM)'
						WHEN (
								@TimeAltKey >= 30000
								AND @TimeAltKey <= 65959
								)
							THEN 'Early Morning(03:00 AM To 6:59 AM)'
						WHEN (
								@TimeAltKey >= 70000
								AND @TimeAltKey <= 85959
								)
							THEN 'AM Peak (7:00 AM To 8:59 AM)'
						WHEN (
								@TimeAltKey >= 90000
								AND @TimeAltKey <= 115959
								)
							THEN 'Mid Morning (9:00 AM To 11:59 AM)'
						WHEN (
								@TimeAltKey >= 120000
								AND @TimeAltKey <= 135959
								)
							THEN 'Lunch (12:00 PM To 13:59 PM)'
						WHEN (
								@TimeAltKey >= 140000
								AND @TimeAltKey <= 155959
								)
							THEN 'Mid Afternoon (14:00 PM To 15:59 PM)'
						WHEN (
								@TimeAltKey >= 50000
								AND @TimeAltKey <= 175959
								)
							THEN 'PM Peak (16:00 PM To 17:59 PM)'
						WHEN (
								@TimeAltKey >= 180000
								AND @TimeAltKey <= 235959
								)
							THEN 'Evening (18:00 PM To 23:59 PM)'
						WHEN (@TimeAltKey >= 240000)
							THEN 'Previous Day Late Night
(24:00 PM to ' + cast(@Size AS VARCHAR(10)) + ':00 PM )'
						END

				-- print @DayTimeBucket 
				INSERT INTO DimTime (
					[TimeKey]
					,[TimeAltKey]
					,[Time30]
					,[Hour30]
					,[MinuteNumber]
					,[SecondNumber]
					,[TimeInSecond]
					,[HourlyBucket]
					,[DayTimeBucketGroupKey]
					,[DayTimeBucket]
					)
				VALUES (
					@k
					,@TimeAltKey
					,@Time30
					,@hour
					,@minute
					,@Second
					,@TimeInSeconds
					,@HourBucket
					,@DayTimeBucketGroupKey
					,@DayTimeBucket
					)

				SET @second = @second + 1
				SET @k = @k + 1
			END

			SET @minute = @minute + 1
			SET @second = 0
		END

		SET @hour = @hour + 1
		SET @minute = 0
	END
END
GO

EXEC [FillDimTime]
GO

SELECT *
FROM DimTime

It works exactly as you describe, up until the point "...Power BI Desktop recognizes the timestamp column properly." But somehow it doesn't want to visualize in my column and stacked charts. Line graphs seem to work just fine. It almost makes me think that it's some kind of bug.

Here are screenshots of some of my settings and of my model:

Graph & settingsRelational Model

[Anonymous]

UPDATE

When I convert my stacked column chart and my clustered column chart to respectively a line and stacked column chart and a line and clustered column chart, the axes seem to work fine but my slicers start to fail. Whereas for line graphs the slicers keep working fine. 

For now this will do, but I still find the whole situation rather strange. So it makes me wonder whether anything underlying the report is still not okay.

Any ideas?

[v-yuezhe-msft]

Hi @Anonymous,

What field do you use to create relationship between DimTime table and other tables? Please create a dummy measure in your DimTime table, create stacked column chart or clustered column chart using Time30 field and the measure, then check if the Time30 field displays properly?

Thanks,
Lydia Zhang

[Anonymous · Accepted solution]

Hi @v-yuezhe-msft,

Ok I've done that and it seems to work just fine then. So it might be in the model/relationships as you say.

I use column 'TimeAltKey' in my Time dimension for the relationships between my Time dimension and 2 Fact Tables (Electricity Measurements and Gas Measurements). I've mapped them on each other through 2 simple tables, and here is the result (see picture below). I still do not see what could be the issue here, as these simple tables also seem to work just fine. Distinct counts of all the TimeAltKeys do not seem to show any abnormalities either (96 for 96 quarter-hours in a day for electricity measurements and 24 for 24 hours in a day for gas measurements). 

Mapped TimeAltKeys

[BetterCallFrank]

Hi @Anonymous

thats very strange,

here's the standard behavior that I get from a column of data type "time":

Time labeled correctlydata type set to time

You can share PBIX file if you want - but with direct query mode its hard to debug