Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Shubhkat
Regular Visitor

Filter the table with in time and out time range

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

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

1 REPLY 1
MFelix
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.