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.
ID | Last Transaction | Result | Start time | End Date | End Time |
3530400909093823 | EIMAQC1 | Fail | 02/09/2021 14:58 | 2021-02-09 | 14:59:21 |
3530400913383833 | EIMAQC1 | Pass | 02/09/2021 12:13 | 2021-02-09 | 12:16:08 |
3530410994762253 | EIMAQC1 | Fail | 02/09/2021 15:15 | 2021-02-09 | 15:15:18 |
3530430925023043 | EIMAQC1 | Fail | 02/09/2021 11:48 | 2021-02-09 | 11:51:04 |
3530430925023043 | EIMAQC1 | Pass | 02/09/2021 12:22 | 2021-02-09 | 12:22:58 |
3530430990547963 | EIMAQC1 | Pass | 02/09/2021 11:53 | 2021-02-09 | 11:53:23 |
3530430990547963 | EIMAQC1 | Pass | 02/09/2021 12:16 | 2021-02-09 | 12:16:46 |
3530430990547963 | EIMAQC1 | Fail | 02/09/2021 12:18 | 2021-02-09 | 12:18:51 |
3530460966893243 | EIMAQC1 | Pass | 02/09/2021 11:57 | 2021-02-09 | 12:00:32 |
3530460995785163 | EIMAQC1 | Pass | 02/09/2021 15:17 | 2021-02-09 | 15:20:11 |
3530470965664623 | EIMAQC1 | Pass | 02/09/2021 08:16 | 2021-02-09 | 08:19:18 |
3530480951576003 | EIMAQC1 | Fail | 02/09/2021 14:40 | 2021-02-09 | 14:45:30 |
3530480995607343 | EIMAQC1 | Fail | 02/09/2021 11:41 | 2021-02-09 | 11:46:12 |
3531451017193713 | EIMAQC1 | Pass | 02/09/2021 15:20 | 2021-02-09 | 15:22:44 |
3548390986696023 | EIMAQC1 | Pass | 02/09/2021 15:04 | 2021-02-09 | 15:04:28 |
3548490986176183 | EIMAQC1 | Fail | 02/09/2021 08:32 | 2021-02-09 | 08:33:24 |
3548550903884963 | EIMAQC1 | Fail | 02/09/2021 10:00 | 2021-02-09 | 10:00:29 |
3548550903884963 | EIMAQC1 | Pass | 02/09/2021 10:39 | 2021-02-09 | 10:39:44 |
3548590920725613 | EIMAQC1 | Pass | 02/09/2021 10:28 | 2021-02-09 | 10:29:09 |
3548610957703653 | EIMAQC1 | Pass | 02/09/2021 11:52 | 2021-02-09 | 11:56:20 |
3548620958170993 | EIMAQC1 | Pass | 02/09/2021 08:45 | 2021-02-09 | 08:47:53 |
3548650949527263 | EIMAQC1 | Fail | 02/09/2021 14:30 | 2021-02-09 | 14:35:06 |
3548670921516243 | EIMAQC1 | Pass | 02/09/2021 08:48 | 2021-02-09 | 08:51:36 |
3548670940993833 | EIMAQC1 | Pass | 02/09/2021 11:38 | 2021-02-09 | 11:38:16 |
3548680940582543 | EIMAQC1 | Pass | 02/09/2021 09:49 | 2021-02-09 | 09:53:06 |
3548680949356003 | EIMAQC1 | Fail | 02/09/2021 15:41 | 2021-02-09 | 15:42:20 |
3548680953688013 | EIMAQC1 | Pass | 02/09/2021 15:04 | 2021-02-09 | 15:11:03 |
3548680958981203 | EIMAQC1 | Pass | 02/09/2021 14:00 | 2021-02-09 | 14:04:01 |
3548720959487603 | EIMAQC1 | Fail | 02/09/2021 10:36 | 2021-02-09 | 10:36:46 |
3548720959487603 | EIMAQC1 | Fail | 02/09/2021 10:51 | 2021-02-09 | 10:52:19 |
3548720959487603 | EIMAQC1 | Fail | 02/09/2021 14:25 | 2021-02-09 | 14:25:24 |
3548730903850653 | EIMAQC1 | Pass | 02/09/2021 14:31 | 2021-02-09 | 14:31:31 |
3548730950469363 | EIMAQC1 | Pass | 02/09/2021 10:47 | 2021-02-09 | 10:51:32 |
3548760929481453 | EIMAQC1 | Pass | 02/09/2021 11:00 | 2021-02-09 | 11:00:55 |
3548760961675023 | EIMAQC1 | Pass | 02/09/2021 15:11 | 2021-02-09 | 15:16:46 |
3548770917517953 | EIMAQC1 | Pass | 02/09/2021 15:26 | 2021-02-09 | 15:29:37 |
3548780957849723 | EIMAQC1 | Fail | 02/09/2021 09:46 | 2021-02-09 | 09:46:25 |
3548780957849723 | EIMAQC1 | Fail | 02/09/2021 10:09 | 2021-02-09 | 10:10:05 |
3548780957849723 | EIMAQC1 | Pass | 02/09/2021 11:13 | 2021-02-09 | 11:13:35 |
3561650968258733 | EIMAQC1 | Pass | 02/09/2021 14:49 | 2021-02-09 | 14:54:57 |
Solved! Go to Solution.
// T is the table from your post...
[Pass 1st Time (%)] =
var IDsWithEarliestDates =
// First, get for all visible ID's
// their minimum start times VISIBLE
// in the current context(!!!).
ADDCOLUMNS(
DISTINCT( T[ID] ),
"@MinStartTime",
CALCULATE(
MIN( T[Start Time] )
)
)
var CountOfIDsWherePassIsFirst =
// Calculate the number of ID's
// where the result for the first
// visible date, as calculated above,
// is "pass."
CALCULATE(
DISTINCTCOUNT( T[ID] ),
TREATAS(
IDsWithEarliestDates,
T[ID],
T[Start Time]
),
KEEPFILTERS( T[Result] = "Pass" )
)
var CountOfAllVisibleIDs =
// Get the number of all visible ID's.
DISTINCTCOUNT( T[ID] )
var Result =
DIVIDE(
CountOfIDsWherePassIsFirst,
CountOfAllVisibleIDs
)
RETURN
Result
Be careful - this formula is sensitive to all filters you put on the table, meaning it respects them all, so the calculation above is carried out within the visible set of ID's with all the restricting filters.
// T is the table from your post...
[Pass 1st Time (%)] =
var IDsWithEarliestDates =
// First, get for all visible ID's
// their minimum start times VISIBLE
// in the current context(!!!).
ADDCOLUMNS(
DISTINCT( T[ID] ),
"@MinStartTime",
CALCULATE(
MIN( T[Start Time] )
)
)
var CountOfIDsWherePassIsFirst =
// Calculate the number of ID's
// where the result for the first
// visible date, as calculated above,
// is "pass."
CALCULATE(
DISTINCTCOUNT( T[ID] ),
TREATAS(
IDsWithEarliestDates,
T[ID],
T[Start Time]
),
KEEPFILTERS( T[Result] = "Pass" )
)
var CountOfAllVisibleIDs =
// Get the number of all visible ID's.
DISTINCTCOUNT( T[ID] )
var Result =
DIVIDE(
CountOfIDsWherePassIsFirst,
CountOfAllVisibleIDs
)
RETURN
Result
Be careful - this formula is sensitive to all filters you put on the table, meaning it respects them all, so the calculation above is carried out within the visible set of ID's with all the restricting filters.
@Anonymous , try a measure like
divide(calculate(countrows(Table), filter(Table, Table[Date] = calculate(Min(Table[Date]),allexpect(Table, Table[ID])) && Table[Result] ="Pass")),calculate(countrows(Table),Table[Result] ="Pass"))
Hello, this is my result:
@Anonymous two )) should close after allexcept
check formula again
divide(calculate(countrows(Table), filter(Table, Table[Date] = calculate(Min(Table[Date]),allexpect(Table, Table[ID])) && Table[Result] ="Pass")),calculate(countrows(Table),Table[Result] ="Pass"))
but the result has to be: pass:36, fail: 19 so yield=65%
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 |
---|---|
54 | |
26 | |
23 | |
15 | |
9 |
User | Count |
---|---|
77 | |
61 | |
47 | |
17 | |
12 |