Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Andy_Chandler
Frequent Visitor

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

 

Part TypeLead Time AchievedLead Time TargetTarget SLA
P151051
P1101051
P1151051
P1201051
P251574
P2101574
P2151574
P2201574
P261574
P291574
P2141574
P2191574

 

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

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

14 REPLIES 14
Sean
Community Champion
Community Champion

@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?

 

2016-10-07 - Percentages.png

KHorseman
Community Champion
Community Champion

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

 

passfail.PNG

 

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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)

with P1 set at 49%

 

2016-10-10 - Percentages.png

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

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

2016-10-10 - Percentages 2.png

 

 

 

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?

 

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sean
Community Champion
Community Champion

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

KHorseman
Community Champion
Community Champion

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

 

ianmalcolm.jpeg





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sean
Community Champion
Community Champion

Smiley LOL Smiley LOL Smiley LOL

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

 

Still nowhere near close to this post

Greg_Deckler
Super User
Super User

Just create a third measure based on the previous two,

 

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

psuedo-code.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.