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:
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!
Solved! Go to 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...
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:MMS. 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:
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!
Hi @acartobia,
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
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...
Find out how to participate in the first Power BI 'Can You Solve These?' challenge.
Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.
User | Count |
---|---|
93 | |
59 | |
58 | |
57 | |
56 |