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 the below example only A & C are the eligible users, how can I get this?  I hope this makes sense, can someone please advise on how I can achieve this?

 

Thank you!!

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)

     

13 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    sraj , Create a measure like

     

    countx(Filter(Summarize(Table, Table[USer], "_count", distinctCOUNT(Table[training Name]) , "_countpass", calculate(distinctCOUNT(Table[training Name]), filter(Table, Table[Pass/Fail] ="PASS"))), [_count] = [_countpass] ),[USer])

     

     

    and plot this with user

    • sraj's avatar
      sraj
      Responsive Resident
      4 years ago

      Its a table visual, will this work then?

  • Tutu_in_YYC's avatar
    Tutu_in_YYC
    Super User
    4 years ago

    The easiest way is to create a calculated column that flags the users that have passed all the tests.
    Then depending on how you want to visualize, you can create a measure using the flag.

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