Adjusting output of the filter to capture time ranges that overlap it

[Julian1]

I'm currently working on a project to capture how much time people spend in meetings during the day. 

As an example, I've created the following table visualization showing my meetings for the day:

If I want to know about meeting time spent during the workday, I'd use a filter for my working hours of 9am-5pm:

The problem is that this cuts off the first meeting in the list that goes from 8:30am-9:15am, since it started before the CollaborationStartTime. However, since the meeting ran into my working hours, I want it to show up in the list of meetings for that day. 

I understand that the filter is working as intended in this case. However, when I use a filter like this I wanted to show any appointment that in any way overlapped my 9am-5pm working hours, even if it started before or ended after that time range. Is this possible?

I've spent a few hours trying to figure this out and unfortunately haven't had much success. I'm new to DAX and PowerBI ( though learning a lot 🙂 and would welcome any thoughts.

[amitchandak]

@Julian1 , a flag like

new column = if(([collbration End time] >= time(9:00:00) && [collbration Start time] <=time(17:00:00)),1,0)

[Greg_Deckler]

@Julian1 - Hello again. So, I generally use what I call a Complex Selector for this. https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

Essentially, you implement whatever logic you want and return a 1 or 0 and then filter on that. In your case it might be:

Selector =
  SWITCH(TRUE(),
    [End] > [WorkingStart] && [End] < [WorkingEnd],1,
    [Start] < [WorkingEnd] && [Start] > [WorkingStart],1,
    [Start] > [WorkingStart] && [End] < [WorkingEnd],1,
    0
  )

You may need different logic.

[Julian1]

Thanks, Greg. Would "Select" in this case by a Calculated column on the table with my meeting data? If so, how do I get the DAX query to use the value selected in my filter? That's the part I've been struggling with.

It looks like "WorkingStart" and "Working End" would use the values provided by the filter shown in my screenshot above, just not sure how to make that work. 

I read your link and didn't get how to do that, perhaps just because I'm so new to this. 

[Greg_Deckler · Accepted solution]

@Julian1 - Sorry, "Selector" is a measure, not a calculated column. You would add it into the Filter pane at the visualization level for you table visual or whatever visual you are using.

I guess you could make it a calculated column pretty easily though now that I think about it.

[Julian1]

Thanks, Greg, this really helped. It took a while for me to figure it out but I got it to work.