Forum Discussion
Helper IIICalculate Other Trained Employees
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])))
Resolver IHi, 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