How to use a Power BI slicer to filter two column of a table

Question

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

Answer 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.