Forum Discussion
Measure or a Column question
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)
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 )
Tutu_in_YYC - can I add this clause in too? Users falling in this category and have passed between these dates can be 1 too... those examples I shared had failed before 3/11/2021 date of failure was 12/15/2020
USER Dept Training Name PASS/FAIL Merge A HR WEB PASS 3/1/2022 A HR SOCIAL PASS 11/17/2020 A HR PERSONAL PASS 12/1/2020 A HR PROACTIVE PASS 12/8/2020 B IT WEB FAIL 12/15/2020 B IT SOCIAL FAIL 11/17/2020 B IT PERSONAL PASS 11/9/2021 B IT PROACTIVE PASS 3/1/2022 B IT PASSPHRASE PASS 12/15/2020 B IT INSIDER PASS 12/22/2020 C RESP PROACTIVE PASS 9/7/2021 C RESP PASSPHRASE PASS 10/5/2021 C RESP INSIDER PASS 11/2/2021 D OT WEB FAIL 5/24/2021 D OT SOCIAL PASS 12/22/2020 D OT PERSONAL PASS 2/2/2021 D OT PROACTIVE PASS 3/2/2021 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)Hi there - I thought I was all done with this one but now I am asked to add another clause, which I dont know if it's possible...I may have to give up on this report.
Come to know on top all passes in the last 365 days they want to make sure the user has passed in these two specific quiz (Mega quiz 1 & mega Quiz 2) on the first 7 days of enrollment that's the merged DATE. This is the latest of what we have
Flag =VAR WhichUser = 'User Performance'[Name]VAR CutOffDate = EDATE(TODAY(), -12)VAR NoOfPasses =CALCULATE (COUNT ( 'User Performance'[PASS/FAIL] ),ALL ( 'User Performance' ),'User Performance'[Name] = WhichUser,'User Performance'[PASS/FAIL] = "Passed" ,'User Performance'[Merged] >= CutOffDate)VAR NoOfTrainings =CALCULATE (COUNT ( 'User Performance'[Training Name] ),ALL ( 'User Performance' ),'User Performance'[Name] = WhichUser,'User Performance'[Merged] >= CutOffDate)RETURNIF ( NoOfPasses = NoOfTrainings, 1, 0 )