Date Hierarchy at Hourly Level

[kinseld5]

hi,

Interested in doing a date hierarchy at the current, year, quarter, week, day level, but also introducing hourly data into the hierarchy, but the big concern is all data isnt hourly, only some of it is. for example, if i was monitoring the number of people logged into a warehouse i would have the information as shown, but, the database also holds hourly data for the last two days, and power bi does not read the daily information and hourly information as different formats.

essentially i want to be able to see the weekly, daily data in a line graph and then select the 11/02/2017 and drill down to the hourly data, but also to use the next level in the hierarchy button, which would also allow me to go from all daily data to all availabel hourly data.

if there was a slier that helped with this that would also be great 🙂

any help would be appreciated. thanks

[v-huizhn-msft]

Hi @kinseld5,

You can use this custom visual: HierarchySlicer, please download it and add it in your Power Bi desktop. Please see: Add a custom visual to a report.

Best Regards,
Angelia

[kinseld5]

Hi Angelia,

That slicer is not working at an hourly level, its only going to daily level. The data i have is in both daily and hourly level, one table shows the daily level and another table shows the hourly level. Essentially i need to be able to dig deeper into the data via the two options indicated previously, so for example i might have 10 days daily data (daily table) but only 2 days hourly data (hourly table), i want to be able to select the drill down or next level in hierarchy buttons to go from daily to hourly, so i could go from 10 days hourly warehouse numbers and then by selecting one of the options mentioned i would then move to hourly data which might be made up of the last two days data (48 hours) plus another 10 hours if i ran at 10am on the 11th day.

is something like this possible, because essentially based on whether the time level is daily or hourly the values in the graphs would need to change to reference different tables

Regards,

Dave

[v-huizhn-msft · Accepted solution]

Hi @kinseld5,

You can create a calculated column to get the hours level. For example, I create a calculated column using the formula.

Hour = RIGHT(Table2[Date],10)

Then you can create a Hierarchy,  add it to HierarchySlicer as follows.



Or you can use the hour directly in slicer, please see the following screenshot.



In addition, you can also split the column by blank space to get hours field, but notice it changes the structure of your resource table. The following shows the Query statement and result using my sample table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNNc31zc0VDAwtDIwUIrVQREzwiJmjEXMBIuYKRYxM7BYLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-US"), "Date", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Date.1", "Date.2", "Date.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date.1", type date}, {"Date.2", type time}, {"Date.3", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Date.3"})
in
    #"Removed Columns"

Best Regards,
Angelia

[kinseld5]

Hi Angelia,

In relation to the first image, where you have Date, Year, Month, Day and Hour, do i need to make the fields manually for Year, Month and Day (understand i need to do hourly myself). think this is the method i want, so thanks for the reply, just need clarification on the above if you dont mind.

Cheers,

Dave

[v-huizhn-msft]

Hi @kinseld5,

If you value is date type, you can use it directly in hierarchy, don't need to create Year, Month and Day. Please test and mark the right reply as answer.

Thanks,
Angelia

[Greg_Deckler]

Perhaps import the hourly data as a different table with a relationship to your other table based upon date. And then use 2 visuals so that as you drill down into the non-hourly data you get to a particular day and then you can drill into or display your hourly data? 

[kinseld5]

thanks for the response but dont see how i can set up a relationship between the two tables as in alot of cases there are numerous warehouses, i.e. warehouse a, warehouse b, etc, and its not possible to link tables without one of them having some unique value right?

im really interested in having the ability to use the 'drill down' and 'next level in the hierarchy' features of the graph, as opposed to having in two seperate graphs.