Time table issue in PowerBI - (for stacked bar chart)

Question

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.

PowerBI printscreen

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")
hora2 = time(Hora[hour];0;0) 

This is what I want to achieve! Purpose ! but for some reason the data is not shown properly. I am verifying it with SQL queries. And I believe it is because the time table is related as many-many on both sides with the fact table. The time table doesn't have unique values, but I think it should have just as the calendar table. Now I am using the time table with the DAX code (3).

Thanks!

Answer 1

You could simply add an "Hour" calculated column to your fact table, using DAX:

Hour = HOUR ( FactTable[I_Time] )

Or you can generate a TIME table using this M code (a simplified version of #2, in your question), and create a relationship to the Time column of your fact table:

let
    Source = List.Times(#time(0, 0, 0), 1440, #duration(0,0,1,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Time"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Time", type time}}),
    #"Inserted Hour" = Table.AddColumn(#"Changed Type", "Hour", each Time.Hour([Time]), Int64.Type)
in
    #"Inserted Hour"