Forum Discussion

sraj's avatar
sraj
Responsive Resident
4 years ago
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...
Need Help
  • Tutu_in_YYC's avatar
    Tutu_in_YYC
    4 years ago

    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's avatar
    Tutu_in_YYC
    4 years ago

    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)

     

sraj's avatar
sraj
Responsive Resident
4 years ago
USERDeptTraining NamePASS/FAILMerge
     
AHRWEBPASS3/1/2022
AHRSOCIALPASS11/17/2020
AHRPERSONALPASS12/1/2020
AHRPROACTIVEPASS12/8/2020
BITWEBFAIL12/15/2020
BITSOCIALFAIL11/17/2020
BITPERSONALPASS11/9/2021
BITPROACTIVEPASS3/1/2022
BITPASSPHRASEPASS12/15/2020
BITINSIDERPASS12/22/2020
CRESPPROACTIVEPASS9/7/2021
CRESPPASSPHRASEPASS10/5/2021
CRESPINSIDERPASS11/2/2021
DOTWEBFAIL5/24/2021
DOTSOCIALPASS12/22/2020
DOTPERSONALPASS2/2/2021
DOTPROACTIVEPASS3/2/2021
Tutu_in_YYC's avatar
Tutu_in_YYC
Super User
4 years ago

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)

 

  • sraj's avatar
    sraj
    Responsive Resident
    4 years ago

    Tutu_in_YYC - 

    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

    )

    RETURN
    IF ( NoOfPasses = NoOfTrainings, 1, 0 )

     

    • Tutu_in_YYC's avatar
      Tutu_in_YYC
      Super User
      4 years ago

      Hi
      what do you mean by  "on the first 7 days of enrollment" ? Is merge the registration date or passing/fail date?

      • sraj's avatar
        sraj
        Responsive Resident
        4 years ago

        Merge date is the enrollment date of the user and  "on the first 7 days of enrollment"  means 7 days from the enrollment date.