Forum Discussion

Andy_Chandler's avatar
Andy_Chandler
Frequent Visitor
9 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
    9 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"
)
Andy_Chandler's avatar
Andy_Chandler
Frequent Visitor
9 years ago

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's avatar
Sean
Icon for Community Champion rankCommunity Champion
9 years ago

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's avatar
    Andy_Chandler
    Frequent Visitor
    9 years ago

    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?

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 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"
)