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

Do you mind showing how I can get the calculated column for this?  Just showing on a table view

Tutu_in_YYC's avatar
Tutu_in_YYC
Super User
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 )


 

  • sraj's avatar
    sraj
    Responsive Resident
    4 years ago

    That worked, I had other exceptions on there due to which I didnt see it all....but it works beautifully!! Thank you!!

  • sraj's avatar
    sraj
    Responsive Resident
    4 years ago

    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

     

     

    • 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)