Search code examples
powerbidax

Enhancing Slicer Date Granularity Selection to Include Time

_Hi, I'm interested in expanding the granularity options available in my slicer, which is currently based on my 'Dates' Table. I'd like to include options for 6-hour, 12-hour, and 48-hour granularities.
Could you advise me on the best approach for achieving this?
Currently, I've attempted the following :

  • My 'Dates' Table is related to my 'EDV_View2' facts table with a 1to* relationship
  • I created a disconnected 'TimeTable' and performed a crossjoin with my 'Dates' table to generate a dynamic 'Dates&TimeTable' containing data for the last 48 hours. I then established a 1to* relationship between 'Dates&TimeTable' and 'EDV_View2'
  • However, when I try to set 'Dates&TimeTable' as a Date Table, it doesn't work.
  • For visualization, I created a chart with 'Dates&TimeTable'[Dates] and 'Dates&TimeTable'[Time] on the X-axis and the following measure on the Y-axis :
    TEST SUM Conso CPT Selected A AFF =
      VAR _Maxdate = CALCULATE(MAX(EDV_View2[Date&Time]), ALL())
      Var _Mindate = _Maxdate - TIME(12, 0, 0)
      VAR _Start = CALCULATE(_Mindate, REMOVEFILTERS(EDV_View2))
      VAR _End = CALCULATE(_Maxdate, REMOVEFILTERS(EDV_View2))
      RETURN
      IF(
        MIN( EDV_View2[Date&Time] ) >= _Start &&
        MAX( EDV_View2[Date&Time] ) <= _End &&
        [SUM Conso CPT Selected A] <> 0,
        [SUM Conso CPT Selected A]
      )
  • However, despite generating the chart, my period selection functionality doesn't seem to be working. Could this be due to the Date Table setting ? enter image description here

Thanks in advance for your help and advice. Here is my Pbix file

Solution

  • This is a granularity problem. You need to create a table for time granularity that you can combine with your facts table [time] column.

    It will determine which bucket your time period is assigned to.

    For example:

    05:00 06:00 will be in the first 6h,8h,12h bucket

    08:00 09:00 will be in the second 6h,8h, but first 12h bucket

    I did this by creating a time table for second intervals you can modify it for minutes or hours. Total seconds in a day = 86400. You are creating a time table(dimension) that has every second of the day in it.

    let

    // rank each second in a day 0=00:00:00 .. 86399=23:89:59
Source = {0..86399},

#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", Int64.Type}}),

#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Second number"}}),
 // divide it by the total seconds of the day to get the(power query, excel time value
#"Inserted Division" = Table.AddColumn(#"Renamed Columns", "Division", each [Second number] / 86400, type number),
//change data type to time
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted Division",{{"Division", type time}}),
// Get time to the second: ex. the if Second number = 5 then second number = 00:00:05
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Division", "Time to the second"}}),
// create buckets
#"Inserted Integer-Division" = Table.AddColumn(#"Renamed Columns1", "5second bucket", each Number.IntegerDivide([Second number], 5), Int64.Type),

#"Inserted Integer-Division1" = Table.AddColumn(#"Inserted Integer-Division", "10 second bucket", each Number.IntegerDivide([Second number], 10), Int64.Type),

#"Inserted Integer-Division2" = Table.AddColumn(#"Inserted Integer-Division1", "15 second bucket", each Number.IntegerDivide([Second number], 15), Int64.Type),

#"Inserted Integer-Division3" = Table.AddColumn(#"Inserted Integer-Division2", "30 second bucket", each Number.IntegerDivide([Second number], 30), Int64.Type),

#"Inserted Integer-Division4" = Table.AddColumn(#"Inserted Integer-Division3", "1 min bucket", each Number.IntegerDivide([Second number], 60), Int64.Type),

#"Inserted Integer-Division5" = Table.AddColumn(#"Inserted Integer-Division4", "2 min bucket", each Number.IntegerDivide([Second number], 120), Int64.Type),

#"Inserted Integer-Division6" = Table.AddColumn(#"Inserted Integer-Division5", "3 min bucket", each Number.IntegerDivide([Second number], 180), Int64.Type),

#"Inserted Integer-Division7" = Table.AddColumn(#"Inserted Integer-Division6", "5 min bucket", each Number.IntegerDivide([Second number], 300), Int64.Type),

#"Inserted Integer-Division8" = Table.AddColumn(#"Inserted Integer-Division7", "10 min bucket", each Number.IntegerDivide([Second number], 600), Int64.Type),

#"Inserted Integer-Division9" = Table.AddColumn(#"Inserted Integer-Division8", "20 min bucket", each Number.IntegerDivide([Second number], 1200), Int64.Type),

#"Inserted Integer-Division10" = Table.AddColumn(#"Inserted Integer-Division9", "30 min bucket", each Number.IntegerDivide([Second number], 1800), Int64.Type),

#"Inserted Integer-Division11" = Table.AddColumn(#"Inserted Integer-Division10", "40 min bucket", each Number.IntegerDivide([Second number], 2400), Int64.Type),

#"Inserted Integer-Division12" = Table.AddColumn(#"Inserted Integer-Division11", "50 min bucket", each Number.IntegerDivide([Second number], 3000), Int64.Type),

#"Inserted Integer-Division13" = Table.AddColumn(#"Inserted Integer-Division12", "1 hour bucket", each Number.IntegerDivide([Second number], 3600), Int64.Type),

#"Inserted Integer-Division14" = Table.AddColumn(#"Inserted Integer-Division13", "2 hour bucket", each Number.IntegerDivide([Second number], 7200), Int64.Type),

#"Inserted Integer-Division15" = Table.AddColumn(#"Inserted Integer-Division14", "3 hour bucket", each Number.IntegerDivide([Second number], 10800), Int64.Type),

#"Inserted Integer-Division16" = Table.AddColumn(#"Inserted Integer-Division15", "6 hour bucket", each Number.IntegerDivide([Second number], 21600), Int64.Type),

#"Inserted Integer-Division17" = Table.AddColumn(#"Inserted Integer-Division16", "12 hour bucket", each Number.IntegerDivide([Second number], 43200), Int64.Type),
// add names/labels for the buckets
#"Added Custom" = Table.AddColumn(#"Inserted Integer-Division17", "5 second time slot", each [5second bucket] * 5 / 86400),

#"Added Custom1" = Table.AddColumn(#"Added Custom", "10 second time slot", each [10 second bucket] * 10 / 86400),

#"Added Custom2" = Table.AddColumn(#"Added Custom1", "15 second time slot", each [15 second bucket] * 15  / 86400),

#"Added Custom3" = Table.AddColumn(#"Added Custom2", "30 second time slot", each [30 second bucket] * 30  / 86400),

#"Added Custom4" = Table.AddColumn(#"Added Custom3", "1 minute time slot", each [1 min bucket] * 60 / 86400),

#"Added Custom5" = Table.AddColumn(#"Added Custom4", "2 minute time slot", each [2 min bucket] * 120  / 86400),

#"Added Custom6" = Table.AddColumn(#"Added Custom5", "3 minute time slot", each [3 min bucket] * 180 / 86400),

#"Added Custom7" = Table.AddColumn(#"Added Custom6", "5 minute time slot", each [5 min bucket] * 300 / 86400),

#"Added Custom8" = Table.AddColumn(#"Added Custom7", "10 minute time slot", each [10 min bucket] * 600  / 86400),

#"Added Custom9" = Table.AddColumn(#"Added Custom8", "15 minute time slot", each [20 min bucket] * 1200  / 86400),

#"Added Custom10" = Table.AddColumn(#"Added Custom9", "30 minute time slot", each [30 min bucket] * 1800 / 86400),

#"Added Custom11" = Table.AddColumn(#"Added Custom10", "40 minute time slot", each [40 min bucket] * 2400 / 86400),

#"Added Custom12" = Table.AddColumn(#"Added Custom11", "50 minute time slot", each [50 min bucket] * 3000 / 86400),

#"Added Custom13" = Table.AddColumn(#"Added Custom12", "1 hour time slot", each [1 hour bucket] * 3600 / 86400),

#"Added Custom14" = Table.AddColumn(#"Added Custom13", "2 hour time slot", each [2 hour bucket] * 7200 / 86400),

#"Added Custom15" = Table.AddColumn(#"Added Custom14", "3 hour time slot", each [3 hour bucket] * 10800 / 86400),

#"Added Custom16" = Table.AddColumn(#"Added Custom15", "6 hour time slot", each [6 hour bucket] * 21600 / 86400),

#"Added Custom17" = Table.AddColumn(#"Added Custom16", "12 hour time slot", each [12 hour bucket] * 43200 / 86400),
// remove the time slots, you won't need them
#"Removed Columns" = Table.RemoveColumns(#"Added Custom17",{"5second bucket", "10 second bucket", "15 second bucket", "30 second bucket", "1 min bucket", "2 min bucket", "3 min bucket", "5 min bucket", "10 min bucket", "20 min bucket", "30 min bucket", "40 min bucket", "50 min bucket", "1 hour bucket", "2 hour bucket", "3 hour bucket", "6 hour bucket", "12 hour bucket"}),
// change data type to time
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"12 hour time slot", type time}, {"6 hour time slot", type time}, {"3 hour time slot", type time}, {"2 hour time slot", type time}, {"1 hour time slot", type time}, {"50 minute time slot", type time}, {"40 minute time slot", type time}, {"30 minute time slot", type time}, {"15 minute time slot", type time}, {"10 minute time slot", type time}, {"5 minute time slot", type time}, {"3 minute time slot", type time}, {"2 minute time slot", type time}, {"1 minute time slot", type time}, {"30 second time slot", type time}, {"15 second time slot", type time}, {"10 second time slot", type time}, {"5 second time slot", type time}}),

    in #"Changed Type2"

    Then you can add it to your facts table column [time] by the column [Time to the second]. And you should be set.

    check how to use parameters and SELECTEDVALUE() and add it to your x axis. *

    you can also check out these videos: *here is the idea, the concept: https://www.youtube.com/watch?v=oa0sBSJBFe0

    here is the actual date date/time table:

    https://www.youtube.com/watch?v=KuPmDB4nf-g

    https://www.youtube.com/watch?v=-q7v56p192M