Filter a table based on two dates column, within the context of a Date slicer

[Anonymous]

I'm trying to display a table with the names of employees that were active during a period defined by a date slicer. It seems so trivial, yet I can't figure it out. I hope somebody can help.

My (simplified) table [employees] looks something like this:

[startDate], [endDate], [name]

All I want to do is just show the names of the employees with a [startDate] before the end of the defined period, and an [endDate] after the beginning of the period. 

The furthest I've come so far is with measure used in a visual filter. The measure looks like this:

Active Employee (filter) =
VAR Begin_of_Period =
    MIN ( date[date] )
VAR End_of_Period =
    MAX ( date[date] )
RETURN
    IF (
        MAX ( employees[startDate] ) <= End_of_Period
            && MAX ( employees[endDate] ) >= Begin_of_Period,
        1,
        0
    )

NOTE: [endDate] always has a value at the moment, so I don't have to check for blanks.

I use the measure in a visual filter on 'is 1'. That seems to work for the current year. But when I select 2020 it doesn't. 

Obviously, I don't want to use MAX() in this measure, but I can't figure out how to do an IF() without something like this, and I was told this would 'trick' PowerBI. Unfortunately, I'm not so lucky. I suspect it's taking the MAX() of the [startDate] and [endDate] of the dataset, and doing some magic there.

I've also tried something with a summarized column and trying to feed that as a value in the table, but that didn't work as well. 

I have the feeling I'm overlooking something obvious, but I can't seem to figure out what it is. I hope there's somebody out there who could point me in the right direction. 

[Jihwan_Kim]

Hi, @Anonymous 

Please correct me if I wrongly understood your question.

It seems that your measure is correct.

Please check if your calendar-dim-table and fact table are connected. It has to be NOT CONNECTED.

Or, please share your sample pbix file's link then I can try to have a look into it to find a solution.

thanks.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[Anonymous]

Hi @Jihwan_Kim , thanks for your help! Much appreciated.

My apologies for the delay in reply. I had to create a clean file with the simplified data. That took a bit longer than I hoped. 

Good to hear the measure seems correct. Can you confirm that this will indeed 'trick' powerBI in thinking I'm doing a calculation, where in fact, it's just returning the cell value? 

And to answer your question; there's no relation between my date table and this table. I interpret your question to say that if there had been a relation, the date slicer would filter on these dates as well, is that correct? (forgive my ignorance, I'm fairly new to PowerBI).

I did figure out something while making the simplified file; the problem seems to occur because I'm not displaying [startDate] or [endDate] in the table. If I do select one of both, it does seem to work (haven't fully debugged yet, but the data seems correct at first glance). Do you know of a fix for this other than showing the columns? (which is not really an option)

I've shared a sample .pbix below. I greatly appreciate all the help you have to offer. 

https://drive.google.com/file/d/19n4lJKaGmXEAsNLr45jQBxI_nsv6my7v/view?usp=sharing

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the link down below.

I created two measures.

If it is OK with you, can you draw me tables or charts that do you want to display?

https://www.dropbox.com/s/o9zdeqs1iabfs6s/ActiveEmployees_dummy.pbix?dl=0 

[Anonymous]

Hi @Jihwan_Kim,

I think you've done it! (Both a filter on the count measure (> 0), or the on the flag measure (is "Active") seem to do the trick, also If I don't have [startDate] or [endDate] added to the values of my table. That's great!

I don't really understand your question about drawing the tables or charts, though. There's not much to draw I'm afraid. In this context, it's as simple as just showing the names of the active employees in a table. (In the real report, a table with these names is used to display the number of hours booked on different hour types over a specific period of time for example).

That simple view wasn't working if I didn't add [startDate] or [endDate] to the table to display (which shows in my pbix file). But when I use one of your measures, it does work!

Maybe I'm stretching and asking too much, but could you explain why the difference in approach makes a difference to PowerBI? I'm just getting started to get used to the way PowerBI 'thinks', so It would help a lot. Your approach to use a count to flag all (in)active users feels much more elegant than my solution, I just don't understand why.

I'll mark your answer as the solution when I'm sure you don't have any additional questions of your own. In any case; thanks a lot for the help! 

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

I just wanted to know if you needed to have different charts or visualizations. Then it would be much helpful for me to understand what you needed if you could draw pictures of what you wanted to see.

Thank you.