cancel
Showing results for
Did you mean:
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 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

1 ACCEPTED SOLUTION
Community Champion

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
SUMMARIZE(
Parts,
Parts[RowID]
),
"pass",
)
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(
Parts,
"pass",
),
[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"
)```

Proud to be a Super User!

14 REPLIES 14
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?

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
SUMMARIZE(
Parts,
Parts[RowID]
),
"pass",
)
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.

Proud to be a Super User!

Community Champion

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 =
SUMMARIZE(
Parts,
Parts[RowID]
),
"pass",
)
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"
)```

Proud to be a Super User!

Frequent Visitor

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)

Community Champion

with P1 set at 49%

Frequent Visitor

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

Community Champion

Okay the only way I can quickly think of to do this is with calculated 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"
)```

Frequent Visitor

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?

Community Champion

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
SUMMARIZE(
Parts,
Parts[RowID]
),
"pass",
)
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(
Parts,
"pass",
),
[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"
)```

Proud to be a Super User!

Community Champion

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

Proud to be a Super User!

Community Champion

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

Community Champion

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

Proud to be a Super User!

Community Champion

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

Super User IV

Just create a third measure based on the previous two,

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

psuedo-code.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Announcements