topic Re: First pass yield??? in DAX Commands and Tips

First pass yield???

Anonymous — Thu, 15 Apr 2021 14:37:47 GMT

Hello everyone, I need help from you at how can I get the % pass that occurred the first time for each ID? Considering all the fields in the table.

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

Re: First pass yield???

amitchandak — Thu, 15 Apr 2021 15:53:23 GMT

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

Re: First pass yield???

Anonymous — Thu, 15 Apr 2021 16:13:37 GMT

Hello, this is my result:

Re: First pass yield???

amitchandak — Thu, 15 Apr 2021 17:07:05 GMT

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

Re: First pass yield???

Anonymous — Thu, 15 Apr 2021 19:43:03 GMT

but the result has to be: pass:36, fail: 19 so yield=65%

Re: First pass yield???

Anonymous — Thu, 15 Apr 2021 22:00:29 GMT

// 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.