Calculate Other Trained Employees

[bernate]

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)

I want to create a visual that will show below:

I wrote this measure for Other Trained: 

Other Trained = CALCULATE(DISTINCTCOUNT('Training'[Employee ID]),ALL('Training'[Training Number]), ALLEXCEPT('Training', 'Training'[Training Number],'Training'[Training Name]),'Training'[Training Status] = "Complete")

But when replace the Employee ID column from the Training Table with the Employee Name from the Employees Table, all of the employees show up even if they are not part of the training. What do I need to fix in the measure so that I can use the Employee Name in the visual instead of the Employee ID?

[v-tejrama]

Hi @bernate ,

Thank you  @Khashayar and  @MasonMA  for the response provided! 

Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you for your understanding!

[Khashayar]

Hi, hope my answer can help you.

You're removing the Employee filter in your current measure (the ALLEXCEPT('Training', 'Training'[Training Number],'Training'[Training Name]) call explicitly keeps only Training Number / Name and drops the Employee ID filter). When you switch the visual to show Employees[Employee Name] the filter flows from Employees → Training, but your measure then removes that flow so every employee appears.

Fix: keep the Employee filter in your measure. The easiest change is to include Training[Employee ID] in the ALLEXCEPT so the employee filter coming from the Employees table is preserved.

Try this:

Other Trained =

CALCULATE(

    DISTINCTCOUNT( 'Training'[Employee ID] ),

    ALLEXCEPT(

        'Training',

        'Training'[Training Number],

        'Training'[Training Name],

        'Training'[Employee ID]    // <--- preserve employee filter

    ),

    'Training'[Training Status] = "Complete"

)

Explanation

• Including Training[Employee ID] in ALLEXCEPT prevents your measure from wiping out the employee filter that flows from Employees[Employee Name].
• If you truly only want to remove the filter on the training and keep all other filters (including the employee), you can also use REMOVEFILTERS('Training'[Training Number]) (or ALL('Training'[Training Number])) instead of ALLEXCEPT — but be careful not to remove the Employee filter.

Optional (robust) alternative using TREATAS to explicitly apply the employee filter from the Employees table to the Training table:

Other Trained =

CALCULATE(

    DISTINCTCOUNT( Training[Employee ID] ),

    'Training'[Training Status] = "Complete",

    TREATAS( VALUES( Employees[Employee ID] ), Training[Employee ID] ),

    ALLEXCEPT( Training, Training[Training Number], Training[Training Name] )

)

Either approach will let you use Employees[Employee Name] in the visual and have the measure respect only the employees who are actually part of the training. If you want, tell me which exact result you expect (count of other trainees per training, count of trainings per employee, etc.) and I’ll tailor the DAX precisely.

If you found this post helpful, please consider accepting it as the solution so that other members can find it more easily.

Regards,

Khashayar Yazdani | Microsoft MCT

https://www.linkedin.com/in/khashayary/

[Ashish_Mathur]

There should be a Many to One relationship from table 2 to table 1

[MasonMA · Accepted solution]

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]

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]

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
)
)