topic Re: Create a DAX measure for when someone DOES NOT have a certification. in DAX Commands and Tips

Create a DAX measure for when someone DOES NOT have a certification.

CBCKV — Mon, 05 Aug 2024 04:28:03 GMT

Hi,

I am trying to create a measure (or a calculated column) that is a flag for when an employee does not hold a specific certification. For instance, I have a certification called "Workplace First Aid Course" I have assigned this to many employees but I want to identify those that do not have that certification assigned to them at all. Whenever I try to do this the data in my table seems to repeat other unrelated fields such as teams and locations, i.e the flag itself will work ok (displaying 0 for does have it and 1 for does not) but I cant use any filters and the table displays all of our locations and teams repeat for each individual record. For reference this is the DAX I currently have:

Missing Certification Flag =
VAR SelectedCertification = SELECTEDVALUE('Certifications Data'[Certification Name])
RETURN
IF (
ISBLANK (
CALCULATE (
COUNTROWS('Certifications Data'),
'Certifications Data'[Certification Name] = SelectedCertification,
'Certifications Data'[EmployeeID] = MAX('Employees'[EmployeeID]),
ALLSELECTED('Certifications Data')
)
),
1,
0
)

Re: Create a DAX measure for when someone DOES NOT have a certification.

bhanu_gautam — Mon, 05 Aug 2024 06:48:58 GMT

CBCKV , Try using below mentioned measure

Missing Certification Flag =
VAR SelectedCertification = "Workplace First Aid Course" -- Replace with your specific certification name
RETURN
IF (
ISBLANK (
CALCULATE (
COUNTROWS('Certifications Data'),
'Certifications Data'[Certification Name] = SelectedCertification,
'Certifications Data'[EmployeeID] = MAX('Employees'[EmployeeID])
)
),
1,
0
)

Re: Create a DAX measure for when someone DOES NOT have a certification.

CBCKV — Mon, 05 Aug 2024 20:05:39 GMT

Hi Bhanu,

Will I have to do this for each certification individually?

Thanks,