Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count with dynamic filter

Hello,

 

I need some help on a formula dax.

 

I have an Employee table with an ID

I have a Training table with the year of training and the employee ID

 

I would like to have the number of employees who have completed the training for one year. that's Ok, I count ID from the training table.

 

I would also like to have the number of employees who haven't completed training according to the year that the user will have chosen in a filter on the year of the training table.

 

Can you help me please ?

Thanks

  • johnt75's avatar
    johnt75
    4 years ago

    Presuming that the slicer will filter the Training table, you could use

    Not done training =
VAR allEmployees =
    ALL ( 'Employees'[ID] )
VAR doneTraining =
    VALUES ( 'Training'[Employee ID] )
RETURN
    COUNTROWS ( EXCEPT ( allEmployees, doneTraining ) )

1 Reply

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    Presuming that the slicer will filter the Training table, you could use

    Not done training =
VAR allEmployees =
    ALL ( 'Employees'[ID] )
VAR doneTraining =
    VALUES ( 'Training'[Employee ID] )
RETURN
    COUNTROWS ( EXCEPT ( allEmployees, doneTraining ) )