Forum Discussion

bernate's avatar
bernate
Helper III
10 months ago
Solved

Calculate Other Trained Employees

Hello! I have 2 tables: Training and Employees. They are connected on Employee ID with a many to one relationship (many employee ID in Training table to 1 employee ID in Employee table) ...
Need Help
  • MasonMA's avatar
    MasonMA
    10 months ago

    Hi, if it is meant to return the number of distinct employees that completed the same training, you can try this measure, 

    _Other Trained =
    CALCULATE(
        DISTINCTCOUNT('Training'[Employee ID]),
        'Training'[Training Status] = "Complete",
        FILTER(
            'Training',
            'Training'[Training No.] = MAX('Training'[Training No.])
            && 'Training'[Training Name] = MAX('Training'[Training Name])
        )
    )

     

MasonMA's avatar
MasonMA
Super User
10 months ago

Hi, if it is meant to return the number of distinct employees that completed the same training, you can try this measure, 

_Other Trained =
CALCULATE(
    DISTINCTCOUNT('Training'[Employee ID]),
    'Training'[Training Status] = "Complete",
    FILTER(
        'Training',
        'Training'[Training No.] = MAX('Training'[Training No.])
        && 'Training'[Training Name] = MAX('Training'[Training Name])
    )
)

 

bernate's avatar
bernate
Helper III
10 months ago

Thank you MasonMA, this worked! Quick follow-up question- in my real data I have 24 employees who have completed training # 100 and training name A. I want the Other Trained column to show the number 23 (24 total trained - the current row of the employee we are looking at). Is there a way to show that instead of the 1 value that gets summed at the botton?

  • MasonMA's avatar
    MasonMA
    Super User
    10 months ago

    Hi, 

     

    You may try adjusted Measure as below, i think it would keep the row’s Training No. and Training Name context via those MAX variables and remove the Employee filter using <> CurrentEmployee.

     

    VAR CurrentTrainingNo = MAX('Training'[Training No.])
    VAR CurrentTrainingName = MAX('Training'[Training Name])
    VAR CurrentEmployee = MAX('Training'[Employee ID])
    RETURN
    CALCULATE(
    DISTINCTCOUNT('Training'[Employee ID]),
    'Training'[Training Status] = "Complete",
    FILTER(
    'Training',
    'Training'[Training No.] = CurrentTrainingNo &&
    'Training'[Training Name] = CurrentTrainingName &&
    'Training'[Employee ID] <> CurrentEmployee
    )
    )