Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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).
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
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
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
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:
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?
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
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).
Hi @Anonymous
thats very strange,
here's the standard behavior that I get from a column of data type "time":
You can share PBIX file if you want - but with direct query mode its hard to debug
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |