0

I have a table of Enter and Exit time of some people like this:

Name   | EnterTime | ExitTime
=============================
Tom    | 13:52:00  | 20:55:00

Robert | 15:55:00  | 21:12:00

Sarah  | 19:08:00  | 19:48:00

Jack   | 16:54:00  | 17:32:00

I want to show the user that in a selected point of time who has presence.

In other word, I want to have a dateTime slicer that the user will pick a time and then the table got filtered to show only rows with:

EnterTime < selectedTime < ExitTime
HamedH
  • 2,814
  • 1
  • 26
  • 37

1 Answers1

1

This can be done with a few steps.

First, we need a list of dates to go into our slicer. If you're using a separate date table, then that's a great source. Otherwise, you can build one with DAX that will contain all the dates in your EnterTime and ExitTime columns.

Click on 'Create Table' in the modeling ribbon. Put in the following DAX:

DateListTable = UNION(
    SELECTCOLUMNS('MyTable', "DateList", 'MyTable'[EnterTime]),
    SELECTCOLUMNS('MyTable',"DateList", 'MyTable'[ExitTime])
)

You now have a slicer and a table visual. Select the slicer and then navigate into the Format Ribbon (it's only available when the visual is selected. Use Edit Interactions to turn off the Slicer's filtering on the table. Set it to no interaction (the circle with a diagonal line through it).

Create a new measure 

ShowDate = 

VAR SelectedDate = SELECTEDVALUE('DateListTable'[DateList], BLANK())

Return 
    if(SelectedDate >= Max('MyTable'[EnterTime ]) && SelectedDate <= max('MyTable'[ExitTime]), 1, 0)

Finally, filter your table visual on the new measure, ShowDate, for when it is 1.

So, we create an independent slicer with a complete list of dates. But we break any filter relationship between it and the table. Instead, we use 'SelectedValue' to capture the value chosen from the filter, and create a dax measure that shows '1' when the value in the filter is between EnterTime and Exit time. Filter on that dax measure, and we get our desired behavior.

Ryan B.
  • 3,575
  • 2
  • 20
  • 26
  • I tested this and it worked, but does using a measure for these scenarios has any downside? is there any solution available that don't requires to filter all visual by a measure? – HamedH Aug 01 '19 at 09:24
  • Using a filter as a measure only has meaning at a row-level context. So this only has meaning on leaf-level tables and will not operate correctly in aggregate. – Ryan B. Aug 01 '19 at 16:14