Forum Discussion
Solved
Measure or a Column question
Hi, Not sure if I need a measure or a calculated column. Looking for only those users who have PASSED in all the trainings they were enrolled in, which varies from 4, 5 or 6 any number. So in t...
Here is the calculation in DAX for the calculated column. It returns 1 if the user passed all the trainings. Let me know if it works.
Flag =
VAR WhichUser = 'Table'[User]
VAR NoOfPasses =
CALCULATE (
COUNT ( 'Table'[PASS/FAIL] ),
ALL ( 'Table' ),
'Table'[User] = WhichUser,
'Table'[PASS/FAIL] = "PASS"
)
VAR NoOfTrainings =
CALCULATE (
COUNT ( 'Table'[Training Name] ),
ALL ( 'Table' ),
'Table'[User] = WhichUser
)
RETURN
IF ( NoOfPasses = NoOfTrainings, 1, 0 )
This includes a clause where it will ignore any fails older than 1 year
Flag =
VAR WhichUser = 'Table'[User]
VAR CutOffDate = EDATE(TODAY(), -12)
VAR NoOfPasses =
CALCULATE(
COUNT('Table'[PASS/FAIL]),
ALL('Table'),
'Table'[User] = WhichUser,
'Table'[PASS/FAIL] = "PASS",
'Table'[Merge] >= CutOffDate
)
VAR NoOfTrainings =
CALCULATE(
COUNT('Table'[Training Name]),
ALL('Table'),
'Table'[User] = WhichUser,
'Table'[Merge] >= CutOffDate
)
RETURN
IF( NoOfPasses = NoOfTrainings, 1, 0)
Merge date is the enrollment date of the user and "on the first 7 days of enrollment" means 7 days from the enrollment date.
Then we are missing a column for [Pass Date] (based on the data provided in previous post). Since we need to compare passing date with enrolment date. Do we have that column?