Time table issue - (for stacked bar chart)

[Anonymous]

Thank you for your time, in advance!

I am using DirectQuery for SQL. Data cannot be imported because the ddbb is too huge. In that main/fact table I have a column "date"-dd/MM/yyyy and a column "time"-HH: MM: SS. I have calculated a calendar table for dates and I have tried to calculate a time table using different solutions that I've found on internet with M or DAX. But none time table is working properly. For example: filtering by "the hour 8" it would have to highlight all times between 8:00:00 and 8:59:59, but it is not showing me all the data entries in that time range.

I need to make a piled bar chart/stacked bar chart: X-axis:HOUR Y-axis:number of operations gruped by languages. But for some reason not all the operations are shown in the chart. I believe the calculated time table is not filtering properly the time column from my fact table.

Note: I do have the latest version of PowerBI-november.

*Note: For the time table I have used these ways:

1. in M:

let

Source = List.Times(#time(0,0,0) , 1440, #duration(0,0,1,0)),
convertToTable = Table.FromList(Source, Splitter.SplitByNothing(), {"DayTime"}, null, ExtraValues.Error),
createTimeKey = Table.AddColumn(convertToTable, "TimeKey", each Time.ToText([DayTime], "HHmmss")),
hourIndex = Table.AddColumn(createTimeKey, "HourIndex", each Time.Hour([DayTime])),
minuteIndex = Table.AddColumn(hourIndex, "MinuteIndex", each Time.Minute([DayTime])),
setDataType = Table.TransformColumnTypes(minuteIndex,{{"DayTime", type time}, {"TimeKey", type text}, {"HourIndex", Int64.Type},
{"MinuteIndex", Int64.Type}})

in
setDataType

2. a more complex M code I've found. I don't need this kind of detail but I did not modify it because I saw it didn't work.

let CreateTimeTable = () as table =>
let
 // Similar to our CreateDateTable script, we start with the smallest unit of the dimension, minute
 // There are a fixed number of minutes in a day, so no need for parameters here
 // 525,600 minutes divided by 365 days in a year = 1440 minutes in a day. 
 // Who says we never learn from Broadway musicals?
 MinuteCount = 1440,
 // Now create a Time type list for a total of 1440 minutes, incrementing one minute at a time
 Source = List.Times(#time(0, 0, 0),MinuteCount, #duration(0,0,1,0)),
 // Turn that list into a one column table
 TableFromList = Table.FromList(Source, Splitter.SplitByNothing()), 
 // Change that table's one column to type Time
 ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type time}}),
 // Rename column to Time
 RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Time"}}),
 // Start inserting columns for each unit of time to represent in the dimension
 InsertHour = Table.AddColumn(RenamedColumns, "Hour", each Time.StartOfHour([Time])),
 InsertMinute = Table.AddColumn(InsertHour, "Minute", each Time.Minute([Time])),
 ChangedTypeHour = Table.TransformColumnTypes(InsertMinute,{{"Hour", type time}}),
 // Creating levels in the hierarchy that might be useful for reporting. Omit if not useful to yours
 InsertQuarterHour = Table.AddColumn(ChangedTypeHour, "Quarter Hour", each if [Minute]<15 then [Hour] else if [Minute] < 30 then Value.Add([Hour],#duration(0,0,15, 0)) else if [Minute] < 45 then Value.Add([Hour],#duration(0,0,30, 0)) else Value.Add([Hour],#duration(0,0,45, 0))),
 ChangedTypeQtrHr = Table.TransformColumnTypes(InsertQuarterHour,{{"Quarter Hour", type time}}),
 ReorderedColumns = Table.ReorderColumns(ChangedTypeQtrHr,{"Time", "Hour", "Quarter Hour", "Minute"}),
 InsertHourNumber = Table.AddColumn(ReorderedColumns, "Hour Number", each Time.Hour([Time])),
 NextHour = Table.AddColumn(InsertHourNumber, "Next Hour", each Value.Add([Hour],#duration(0,1,0, 0))),
 NextQuarterHour = Table.AddColumn(NextHour, "Next Quarter Hour", each Value.Add([Quarter Hour],#duration(0,0,15, 0))),
 InsertPeriod = Table.AddColumn(NextQuarterHour, "Period of Day", 
 each if [Hour Number] >= 0 and [Hour Number] < 4 then "After Midnight" else 
 if [Hour Number] >= 4 and [Hour Number] < 8 then "Early Morning" else
 if [Hour Number] >= 8 and [Hour Number] < 12 then "Late Morning" else
 if [Hour Number] >= 12 and [Hour Number] < 16 then "Afternoon" else
 if [Hour Number] >= 16 and [Hour Number] < 20 then "Evening" else "Late Night"),
 InsertPeriodSort = Table.AddColumn(InsertPeriod, "PeriodSort", each 
 if [Hour Number] >= 0 and [Hour Number] < 4 then 0 else 
 if [Hour Number] >= 4 and [Hour Number] < 8 then 1 else
 if [Hour Number] >= 8 and [Hour Number] < 12 then 2 else
 if [Hour Number] >= 12 and [Hour Number] < 16 then 3 else
 if [Hour Number] >= 16 and [Hour Number] < 20 then 4 else 5),
 InsertTimeKey = Table.AddColumn(InsertPeriodSort, "TimeKey", each Time.ToText([Time], "HHmm"), type text)
 in
 InsertTimeKey
in
 CreateTimeTable

3. Finally a simple DAX column I've created in DAX as simple as this:

Hora = VALUES(FactTable[I_Time])

hour = FORMAT(Hora[I_Time]; "HH")

Thanks!

[Anonymous]

Thank you for your time, in advance!

I am using DirectQuery for SQL. Data cannot be imported because the ddbb is too huge. In that main/fact table I have a column "date"-dd/MM/yyyy and a column "time"-HH:MM:SS. I have calculated a calendar table for dates and I have tried to calculate a time table using different solutions that I've found on internet with M or DAX. But none time table is working properly. For example: filtering by "the hour 8" it would have to highlight all times between 8:00:00 and 8:59:59, but it is not showing me all the data entries in that time range.

I need to make a piled bar chart/stacked bar chart: X-axis:HOUR Y-axis:number of operations gruped by languages. But for some reason not all the operations are shown in the chart. I believe the calculated time table is not filtering properly the time column from my fact table.

Note: I do have the latest version of PowerBI-november.

*Note: For the time table I have used these ways:

1. in M:

let

Source = List.Times(#time(0,0,0) , 1440, #duration(0,0,1,0)),
convertToTable = Table.FromList(Source, Splitter.SplitByNothing(), {"DayTime"}, null, ExtraValues.Error),
createTimeKey = Table.AddColumn(convertToTable, "TimeKey", each Time.ToText([DayTime], "HHmmss")),
hourIndex = Table.AddColumn(createTimeKey, "HourIndex", each Time.Hour([DayTime])),
minuteIndex = Table.AddColumn(hourIndex, "MinuteIndex", each Time.Minute([DayTime])),
setDataType = Table.TransformColumnTypes(minuteIndex,{{"DayTime", type time}, {"TimeKey", type text}, {"HourIndex", Int64.Type},
{"MinuteIndex", Int64.Type}})

in
setDataType

2. a more complex M code I've found. I don't need this kind of detail but I did not modify it because I saw it didn't work.

let CreateTimeTable = () as table =>
let
 // Similar to our CreateDateTable script, we start with the smallest unit of the dimension, minute
 // There are a fixed number of minutes in a day, so no need for parameters here
 // 525,600 minutes divided by 365 days in a year = 1440 minutes in a day. 
 // Who says we never learn from Broadway musicals?
 MinuteCount = 1440,
 // Now create a Time type list for a total of 1440 minutes, incrementing one minute at a time
 Source = List.Times(#time(0, 0, 0),MinuteCount, #duration(0,0,1,0)),
 // Turn that list into a one column table
 TableFromList = Table.FromList(Source, Splitter.SplitByNothing()), 
 // Change that table's one column to type Time
 ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type time}}),
 // Rename column to Time
 RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Time"}}),
 // Start inserting columns for each unit of time to represent in the dimension
 InsertHour = Table.AddColumn(RenamedColumns, "Hour", each Time.StartOfHour([Time])),
 InsertMinute = Table.AddColumn(InsertHour, "Minute", each Time.Minute([Time])),
 ChangedTypeHour = Table.TransformColumnTypes(InsertMinute,{{"Hour", type time}}),
 // Creating levels in the hierarchy that might be useful for reporting. Omit if not useful to yours
 InsertQuarterHour = Table.AddColumn(ChangedTypeHour, "Quarter Hour", each if [Minute]<15 then [Hour] else if [Minute] < 30 then Value.Add([Hour],#duration(0,0,15, 0)) else if [Minute] < 45 then Value.Add([Hour],#duration(0,0,30, 0)) else Value.Add([Hour],#duration(0,0,45, 0))),
 ChangedTypeQtrHr = Table.TransformColumnTypes(InsertQuarterHour,{{"Quarter Hour", type time}}),
 ReorderedColumns = Table.ReorderColumns(ChangedTypeQtrHr,{"Time", "Hour", "Quarter Hour", "Minute"}),
 InsertHourNumber = Table.AddColumn(ReorderedColumns, "Hour Number", each Time.Hour([Time])),
 NextHour = Table.AddColumn(InsertHourNumber, "Next Hour", each Value.Add([Hour],#duration(0,1,0, 0))),
 NextQuarterHour = Table.AddColumn(NextHour, "Next Quarter Hour", each Value.Add([Quarter Hour],#duration(0,0,15, 0))),
 InsertPeriod = Table.AddColumn(NextQuarterHour, "Period of Day", 
 each if [Hour Number] >= 0 and [Hour Number] < 4 then "After Midnight" else 
 if [Hour Number] >= 4 and [Hour Number] < 8 then "Early Morning" else
 if [Hour Number] >= 8 and [Hour Number] < 12 then "Late Morning" else
 if [Hour Number] >= 12 and [Hour Number] < 16 then "Afternoon" else
 if [Hour Number] >= 16 and [Hour Number] < 20 then "Evening" else "Late Night"),
 InsertPeriodSort = Table.AddColumn(InsertPeriod, "PeriodSort", each 
 if [Hour Number] >= 0 and [Hour Number] < 4 then 0 else 
 if [Hour Number] >= 4 and [Hour Number] < 8 then 1 else
 if [Hour Number] >= 8 and [Hour Number] < 12 then 2 else
 if [Hour Number] >= 12 and [Hour Number] < 16 then 3 else
 if [Hour Number] >= 16 and [Hour Number] < 20 then 4 else 5),
 InsertTimeKey = Table.AddColumn(InsertPeriodSort, "TimeKey", each Time.ToText([Time], "HHmm"), type text)
 in
 InsertTimeKey
in
 CreateTimeTable

3. Finally a simple DAX column I've created in DAX as simple as this:

Hora = VALUES(FactTable[I_Time])

hour = FORMAT(Hora[I_Time]; "HH")

Thanks!

[v-danhe-msft]

Hi @Anonymous,

From your description, I could not understand how do you want to filter your data table, if you want to filter all times between 8:00:00 and 8:59:59, you could use below formula in dax:

Create a new table:

Table = CALCULATETABLE('Query1',FILTER('Query1',TIME(8,0,0)<='Query1'[DayTime]&&Query1[DayTime]<TIME(9,0,0)))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[Anonymous · Accepted solution]

Hi @v-danhe-msft!

First of all, thank you very much for your answer. 

I've figured out that the relationship between the fact table and the time table was always wrong and it didn't filter properly, and I couldn't aply only your solution because I needed it to be dinamic for all hours. But it helped me to see time formats. 

It was all wrong because my sql time column had nanoseconds. So I just taken out the nanosecond in my sql and voila!! the data conected properly. 

The purpose was this graphic. Now I have the operations by language and hours of the day. The operations made between 17:00:00 and 17:59:59 are stacked up by language in one bar, and so on...