Forum Discussion

ni30's avatar
ni30
Frequent Visitor
7 years ago

Date and Time slicer from Calendar table

Hi,     I have a Calendar table with date and time in my report. My requirement is to create slicers for a date, hour and min for both start and end. Currently, I am able to filter the data by st...
Need Help
v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi jinma78,

 

That's really a problem. It seems we have to add more tables. The main idea of this workaround is capturing the date in a measure. 

 

Best Regards,

jinma78's avatar
jinma78
Icon for Advocate II rankAdvocate II
7 years ago
Hi v-jiascu-msft
Can you provide an example as to what you mean by creating more tables?

Regards
  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi jinma78,

     

    Two tables for Hour and two tables for Minute. I would suggest creating two time table instead. Please refer to the snapshot below.

    startTimeTable =
GENERATESERIES ( TIME ( 0, 0, 0 ), TIME ( 23, 59, 0 ), TIME ( 0, 1, 0 ) )

    Date-and-Time-slicer-from-Calendar-table2

     

     

    Best Regards,

    • ni30's avatar
      ni30
      Frequent Visitor
      7 years ago

      Hi,

       

       

      Thank you for your response. I am still not able to filter using start and end time tables as you have mentioned. When I select the End time the data table vanishes. Please find an example as below. Can you please advise how do I get the data filtered for time.

       

      https://www.dropbox.com/s/h7ue7lcyaz487t9/Test_Table.pbix?dl=0

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi ni30,

         

        1. The [Time] isn't a pure time in your demo. Please refer to the snapshot below. It should be [Time 2].

        Date-and-Time-slicer-from-Calendar-table3

        2. Since the Date and Time are two columns, I would suggest you make some changes like below. Remove one table and delete several relationships.

        Date-and-Time-slicer-from-Calendar-table4

        3. Create a measure.

        Measure =
VAR startTime =
    MIN ( startTimeTable[Value] )
VAR endTime =
    MAX ( endTimeTable[Value] )
RETURN
    IF (
        MIN ( 'Sheet1'[Time 2] ) >= startTime
            && MIN ( Sheet1[Time 2] ) <= endTime,
        1,
        BLANK ()
    )

        4. Add the measure in the Visual Level Filter and set it as "is not blank".

        Date-and-Time-slicer-from-Calendar-table5

        Please also download the demo from the attachment.

         

        Best Regards,