Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Solved! Go to 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 )
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)
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.
Do you mind showing how I can get the calculated column for this? Just showing on a table view
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 - 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
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 |
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)
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
Hi
what do you mean by "on the first 7 days of enrollment" ? Is merge the registration date or passing/fail date?
Merge date is the enrollment date of the user and "on the first 7 days of enrollment" means 7 days from the enrollment date.
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?
That worked, I had other exceptions on there due to which I didnt see it all....but it works beautifully!! Thank you!!
@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
Its a table visual, will this work then?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |