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)

     

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.

  • Tutu_in_YYC's avatar
    Tutu_in_YYC
    Super User
    4 years ago

    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?