Forum Discussion

Andy_Chandler's avatar
Andy_Chandler
Frequent Visitor
10 years ago
Solved

Calculating whether a service level has been met

I have individual rows that record the lead time for different part batches.  Each part type has a service level requirement.  If any part type fails to meet its service level, I want to calculate th...
  • Anonymous's avatar
    Anonymous
    10 years ago

    OK, I found the flaws in my logic. My previous formula ignored row context all over the place. This version gives the results I was attempting before. I don't know if this will work in DirectQuery though, because it includes SUMMARIZE and ADDCOLUMNS. You might be able to get around that if you go into Options and check the box marked "Allow unrestricted measures in DirectQuery Mode."

     

    Pass/Fail = 
IF(
	HASONEVALUE(Parts[Part Type]),
	VAR target = MIN(Parts[Target SLA]) / 100
	VAR passfail = ADDCOLUMNS(
		SUMMARIZE(
			Parts,
			Parts[RowID]
		),
		"pass",
		CALCULATE(MIN(Parts[Lead Time Achieved]) <= MIN(Parts[Lead Time Target]))
	)
	RETURN IF(
		DIVIDE(
			COUNTROWS(FILTER(passfail, [pass] = TRUE)),
			COUNTROWS(passfail)
		) >= target,
		"Pass",
		"Fail"
	),
	VAR partscores = SUMMARIZE(
		Parts,
		Parts[Part Type],
		"score",
		VAR tg = CALCULATE(MIN(Parts[Target SLA])) / 100
		RETURN IF(
			DIVIDE(
				CALCULATE(
					COUNTROWS(
						FILTER(
							ADDCOLUMNS(
								Parts,
								"pass",
								CALCULATE(MIN(Parts[Lead Time Achieved]) <= MIN(Parts[Lead Time Target]))
							),
							[pass] = TRUE
						)
					)
				),
				CALCULATE(COUNTROWS(Parts))
			) >= tg,
			"Pass",
			"Fail"
		)
	)
	RETURN FORMAT(
		DIVIDE(
			CALCULATE(
				DISTINCTCOUNT(Parts[Part Type]),
				FILTER(partscores, [score] = "Pass")
			),
			DISTINCTCOUNT(Parts[Part Type])
		),
		"0%"
	) & " Passed"
)
Anonymous's avatar
Anonymous
Not applicable
10 years ago

It would help if each row had a unique row identifier. You could always enter an index column in the query editor if there isn't already one that just wasn't shown in the sample data. If you had that you could do it all in one measure with no extra calculated columns.

 

Pass/Fail = 
VAR target = MIN(Parts[Target SLA]) / 100
VAR passfail = ADDCOLUMNS(
	SUMMARIZE(
		Parts,
		Parts[RowID]
	),
	"pass",
	CALCULATE(MIN(Parts[Lead Time Achieved]) <= MIN(Parts[Lead Time Target]))
)
RETURN IF(
	DIVIDE(
		COUNTROWS(FILTER(passfail, [pass] = TRUE)),
		COUNTROWS(passfail)
	) >= target,
	"Pass",
	"Fail"
)

 

I don't know how quickly that would work on a truly massive dataset, but if the table has a reasonable number of rows it should be fine.

Anonymous's avatar
Anonymous
Not applicable
10 years ago

It's Friday afternoon. Time to get really goofy.

 

 

This version returns the expected Pass/Fail score for each Part Type. But on any total/subtotal lines that represent multiple part types, it returns the percent of represented parts that passed overall:

 

Pass/Fail = 
VAR passfail = ADDCOLUMNS(
	SUMMARIZE(
		Parts,
		Parts[RowID]
	),
	"pass",
	CALCULATE(MIN(Parts[Lead Time Achieved]) <= MIN(Parts[Lead Time Target]))
)
RETURN IF(
	HASONEVALUE(Parts[Part Type]),
	VAR target = MIN(Parts[Target SLA]) / 100
	RETURN IF(
		DIVIDE(
			COUNTROWS(FILTER(passfail, [pass] = TRUE)),
			COUNTROWS(passfail)
		) >= target,
		"Pass",
		"Fail"
	),
	VAR partscores = SUMMARIZE(
		Parts,
		Parts[Part Type],
		"score",
		VAR target = MIN(Parts[Target SLA]) / 100
		RETURN IF(
			DIVIDE(
				COUNTROWS(FILTER(passfail, [pass] = TRUE)),
				COUNTROWS(passfail)
			) >= target,
			"Pass",
			"Fail"
		)
	)
	RETURN FORMAT(
		DIVIDE(
			CALCULATE(
				DISTINCTCOUNT(Parts[Part Type]),
				FILTER(partscores, [score] = "Pass")
			),
			DISTINCTCOUNT(Parts[Part Type])
		),
		"0%"
	) & " Passed"
)