Filter the table with in time and out time range

[Shubhkat]

Hello,

I have daily punch in and punch out details with the in_time and out_time(hh:mm:ss).

we want to display it like 6:00:00 AM, 7:00:00 AM and so on for all the different tone ranges. For example if the time is between 6:00:00AM to 6:59:59 AM then it should display 6:00:00 AM. I done with it.

Now we want two slicers(one for in time and other for our time) which take all the tiles from 12:00:00 AM to 11:59:59 PM. So I have created the static table with these ranges and make a relationship with the main table. The relationship is working fine.

My requirements is that I want to add two slicers now from these two static table and I want the data of the main table to be filter based on that.

That means if I select in time as 12:00:00 AM and out time as 7:00:00 AM then my table should show all the data between 12:00:00AM to 7:00:00 AM.

At the moment if I do it then it just show for 12:00:00 AM and 7:00:00 AM. not showing for the range.

Also, if let say there is no record on 12:00:00 AM but there is a record on 7:00:00 AM then it show me no record as the data is filtered to 12:00:00 AM first as per the time order.

Can you please help me with this?

Thanks

Shubhkat

[MFelix · Accepted solution]

Hi @Shubhkat 

You need to add a filter to your calculation something similar to:

Filter =

IF(MAX(Table[Time]) >= Min(Table[StartHour]) && MAX(Table[Time]) <= MAX(Table[EndHour]), 1)

Then filter your visualization based on the non blank values, however this needs to be done with disconnected tables.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.