DAX: Filter Measure based, not understanding how to use the correct context (.PBIXincluded)

[Anonymous]

Hi guys,

I made a measure to use as a Filter on top of a visible to view only the Employees that are in service based on a specific Entry Date.

So Entry Date 10/1/2019 should only view Employees in service at that specific date.

File that includes all:

.PBIX File 

With the following measure works when EmployeeID is the unique record, when I set the measure as filter equal to 1 on the table visual. Unfortunately my EmployeeID isn't unique in the table: If Employees' names change, they get a new record with a new BeginDate and EndDate. Somehow this creates problems to be able to view the correct data. Down below I have 'Employee6' that has 3 records, and the measure below does not count a '1' for the third record of 'Employee6'.

PeriodFilter = 

var _Begin = min(Employees[BeginDate]) 
var _End = min(Employees[EndDate]) 

return 
if(
    ( _Begin <= min('Calendar'[Date]) && _Begin <= max('Calendar'[Date])) 
     && ( _End >= min('Calendar'[Date]) && _End >= max('Calendar'[Date]))
    ,1,0
)

Sample Data:

| Employee   | BeginDate  | EndDate    |
|------------|------------|------------|
| Employee1  | 1/30/2020  | 1/3/2021   |
| Employee2  | 9/20/2021  | 1/1/2022   |
| Employee3  | 1/3/2021   | 5/29/2021  |
| Employee4  | 7/5/2021   | 9/20/2021  |
| Employee5  | 11/10/2020 | 7/5/2021   |
| Employee6  | 1/30/2020  | 1/3/2021   |
| Employee6  | 1/4/2021   | 8/1/2021   |
| Employee6  | 8/2/2021   | 12/31/2099 |
| Employee7  | 7/5/2021   | 9/20/2021  |
| Employee8  | 1/5/2015   | 7/5/2021   |
| Employee9  | 9/20/2021  | 12/31/2099 |
| Employee10 | 1/1/2021   | 12/31/2099 |
| Employee11 | 9/8/2017   | 1/3/2021   |
| Employee12 | 1/3/2021   | 9/20/2021  |
| Employee13 | 7/5/2021   | 12/31/2099 |
| Employee14 | 1/5/2015   | 11/10/2020 |
| Employee15 | 1/30/2020  | 9/20/2021  |
| Employee16 | 1/1/2021   | 12/31/2099 |
| Employee17 | 1/5/2015   | 1/3/2021   |
| Employee18 | 9/20/2021  | 12/31/2099 |
| Employee19 | 1/3/2021   | 12/31/2099 |
| Employee20 | 11/10/2020 | 12/31/2099 |

Somehow it does show the correct result when I also drag fields 'BeginDate' and 'EndDate' in the Table. But I do not want to include those fields in the Table. Because it does work with those fields, I started to think I might need a SUMX function instead, that evaluates every record in the table, and gives me the '1' back for the 3rd record of Employee6, by using the following measure:

PeriodFilter_SUMX = 

var _Begin = min(Employees[BeginDate]) 
var _End = min(Employees[EndDate]) 

return 
SUMX(
    Employees,
    if(
    ( _Begin <= min('Calendar'[Date]) && _Begin <= max('Calendar'[Date])) && ( _End >= min('Calendar'[Date]) && _End >= max('Calendar'[Date]))
    ,1,0
    )
)

This also doesn't work, apart that it gives a higher total number when the Date fields are included in the table.

How can I circumvent this problem within a measure? Any suggestions?

[Anonymous]

Works perfect, thanks! Though I don't really understand why this works, and my code does not.

[mahoneypat · Accepted solution]

Please try this measure expression instead.

PeriodFilterNew =
VAR MinDate =
    MIN ( 'Calendar'[Date] )
VAR MaxDate =
    MAX ( 'Calendar'[Date] )
VAR Result =
    COUNTROWS (
        FILTER (
            Employees,
            Employees[BeginDate] <= MaxDate
                && Employees[EndDate] >= MinDate
        )
    )
RETURN
    IF ( Result > 0, 1, 0 )

Pat