Measure or a Column question

[sraj]

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!!

[Tutu_in_YYC · Accepted solution]

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]

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]

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

[Tutu_in_YYC · Accepted solution]

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]

@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]

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

[Tutu_in_YYC · Accepted solution]

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]

@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]

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

[sraj]

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]

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?

[sraj]

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

[amitchandak]

@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]

Its a table visual, will this work then?