Count ID's from a table that are not equal to a measure

[ArmellA]

Hi, 

I'm trying to count how many employees where not there at a specific date which is a slicer/measure

I tried many things with no success. 

IDCount = Calculate(DISTINCTCOUNT(employee[ID]), FILTER(employee,employee[Date]<>Dates[Date])) 

Thanks for your help.

[Anonymous · Accepted solution]

Hi @ArmellA ,

I think Dates table should have a relationship with employee table.

You can try this measure.

IDCount = 
VAR _CountAll =
    CALCULATE ( DISTINCTCOUNT ( employee[ID] ), ALL ( employee ) )
VAR _CountInRange =
    CALCULATE (
        DISTINCTCOUNT ( employee[ID] ),
        FILTER ( ALL ( employee ), employee[Date] = SELECTEDVALUE ( Dates[Date] ) )
    )
RETURN
    _CountAll - _CountInRange

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ]

hi @ArmellA 

try like:

IDCount =

VAR _date = SELECTEDVALUE(Dates[Date])

RETURN

COUNTROWS(

    FILTER(

        employee,

        employee[Date]<>_date

    )

)

the two tables shall be unrelated.