Referencing an unrelated Filter in another Equation

[ehmacc]

Hello! I have an Employment table set up with a filter that relates to a manager rollup. Separately, I have a calculated column on my DimDate Table that is counting the number of employees active on a given date. When I set a filter to the Employment Table for manager=John Smith, I would like the count on the other table to then also include the filter in it's equation, so only count if manager=John Smith. Rather than creating a new count with a filter hard coded for every manager, is there a way to reference the filter in the count equation?  I would like to stay away from hard coding, since it isn't dynamic and I would need one for a bunch of leaders and then maintain a unique page for each. 

Count equation today: 

Active Employees = CALCULATE(distinctcount(Employment[EmployeeNumber]), filter(Employment,  Employment[LastHire]<=DimDate[Date] && OR(Employment[TerminationDate] > DimDate[Date], isblank(Employment[TerminationDate])=TRUE())))

Count equation with filter Hard Coded:

Active Employees = CALCULATE(distinctcount(Employment[EmployeeNumber]), filter(Employment, Employment[Manager]="John Smith" && Employment[LastHire]<=DimDate[Date] && OR(Employment[TerminationDate] > DimDate[Date], isblank(Employment[TerminationDate])=TRUE())))

[Anonymous]

Hi @ehmacc ,

If this selection is from other table, you can use following measure formula to compare with selected records:

Active Employees =
VAR _managerList =
    ALLSELECTED ( Table[Manager] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Employment[EmployeeNumber] ),
        FILTER (
            Employment,
            Employment[Manager] IN _managerList
                && Employment[LastHire] <= DimDate[Date]
                && OR (
                    Employment[TerminationDate] > DimDate[Date],
                    ISBLANK ( Employment[TerminationDate] ) = TRUE ()
                )
        )
    )

Regards,

Xiaoxin Sheng

[ehmacc]

Hey there @Anonymous 

The equation I was using is on Date Table, but the filter I am using is on the Employment table- so trying 

Active Employees =
VAR _managerList =
    ALLSELECTED ( Employment[Manager] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Employment[EmployeeNumber] ),
        FILTER (
            Employment,
            Employment[Manager] IN _managerList
                && Employment[LastHire] <= DimDate[Date]
                && OR (
                    Employment[TerminationDate] > DimDate[Date],
                    ISBLANK ( Employment[TerminationDate] ) = TRUE ()
                )
        )
    )

Didn't filter my numbers down; assuming because it is making a list of its own list... 

[Anonymous]

Hi @ehmacc ,

Can you please share some sample data with expected result for test?

Regards,
Xiaoxin Sheng