Calculating whether a service level has been met

[Andy_Chandler]

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 that the system overall has failed.

Part Type	Lead Time Achieved	Lead Time Target	Target SLA
P1	5	10	51
P1	10	10	51
P1	15	10	51
P1	20	10	51
P2	5	15	74
P2	10	15	74
P2	15	15	74
P2	20	15	74
P2	6	15	74
P2	9	15	74
P2	14	15	74
P2	19	15	74

In this example, P1 has 2 out of 4 batches where Lead Time meets the Lead Time Target for P1 (50%). P2 has 6 out of 8 batches (75%)  The required service level for P1 is 51%, so I want a single result to show "Failed" even though P2 has passed.

I can create measures to show on a visual that P1 has failed (50% < 51%) and P2 has passed (75% > 74%), but I can't work out how to calculate this as a failure overall

[Sean]

@Andy_Chandler

1) First Create a COLUMN Yes/No

Yes/No = IF ('Table1'[Lead Time Achieved]<='Table1'[Lead Time Target], "Yes", "No")

2) Then these 4 MEASURES

Target Achieved = COUNTROWS ( FILTER ('Table1', 'Table1'[Yes/No]="Yes") )

Part Transactions = CALCULATE ( COUNTROWS('Table1'),  ALLEXCEPT('Table1', 'Table1'[Part Type]) )

Rate = DIVIDE ( [Target Achieved], [Part Transactions], 0 )

Pass/Fail = IF ( [Rate] > DIVIDE( SUM(Table1[Target SLA]), [Part Transactions], 0), "Success", "Fail")

I'm assuming Target SLA is formatted as a percentage!

I wonder if the last measure Pass/Fail could be done slightly differently but basically I'm getting the average which if all numbers are the same should give you the same number at the Part Type Level!

@KHorseman how would you handle this? I wonder if there's a more efficient way?

Anyway I think this should give you the results you are looking for?

[KHorseman]

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.

[KHorseman]

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

[Andy_Chandler]

This is close, although if I set the Target SLA to 49 for P1, it calculates 50% overall where it should be 100% (50>49, 75>74)

[Sean]

with P1 set at 49%

[Andy_Chandler]

@Sean I'm happy with being able to measure this part by part, where I'm struggling is with an overall fail if any one of the indivdual part types fails.

[Sean]

Okay the only way I can quickly think of to do this is with calculated COLUMNS!

Again start with Yes/No all these are COLUMNS!

1)

Yes/No = IF ('Table1'[Lead Time Achieved]<='Table1'[Lead Time Target], "Yes", "No")

2)

Part Rate COLUMN = 
DIVIDE (
    CALCULATE (
        COUNTROWS ( FILTER ( Table1, Table1[Yes/No] = "Yes" ) ),
        ALLEXCEPT ( Table1, Table1[Part Type] )
    ),
    CALCULATE ( COUNTROWS ( Table1 ), ALLEXCEPT ( Table1, Table1[Part Type] ) ),
    0
)

3)

Pass/Fail COLUMN = IF ( Table1[Part Rate COLUMN] > Table1[Target SLA], "Success", "Fail" )

4)

AND Then create this MEASURE

Pass/Fail TEST =
IF (
    COUNTROWS ( FILTER ( Table1, Table1[Pass/Fail COLUMN] = "Fail" ) )
        > 0,
    "Fail",
    "Success"
)

[Andy_Chandler]

OK, so I can get this working in local tables using @Sean's suggestion.  My next issue is getting this to work in DirectQuery mode where CALCULATE() is not allowed in a calculated column.  Would that need the measures only approach?

[KHorseman · Accepted solution]

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

[KHorseman]

Oops, yeah I've done some more testing and it was just a coincidence that the right answer was what it produced. Basically it's counting all "pass" values rather than using Part Type as a filtering context in the subtotal. I'll have to rework this a little...

[Sean]

Wow @KHorseman have you heard of decaf I'll review both of these later today or tomorrow

[KHorseman]

@Sean reads my formulas and thinks to himself...

[Sean]

Well I don't have an appropriate reply image ready but - Yep exactly right! I should've known better by now!

Still nowhere near close to this post

[Greg_Deckler]

Just create a third measure based on the previous two,

if(p1=false,false,if(p2=false,false,true)

psuedo-code.