Count Quality Checks Up to Sign Off

[barrymcguigan84]

Hi I work in a quality environment and we need to count the number of checks a caseworker has done up to an including the sign off, for each task.  In the eample in the table below, the correct answer would be 4, as Barry did 4 closure tasks before being signed off.  We don't want to count the number of checks after sign off.  

There isn't a scope to change the way the data is stored or collected.  I have used Power Query to create a new table ('Checks to sign off') and gruped the rows based on task, caseworker etc. This is useful as before grouping I have created a conditional column with an "If Signed Off Column is blank, blank, otherwisde "Check Date and Time", which allows me to get the latest sign off date and count the number of days between the first check and the sign off check in the grouped table.  The issue I have with this is that the count rows aggregation counts all of rows, even after the sign off check. 

I have tried this dax formula: 

100% Reviews to achieve Sign Off = CALCULATE(Count('Quality Checks'[ID]), 'Quality Checks'[Check Date and Time] <= Max('Checks to sign off'[Earliest Signed Off Date & Time]))

Which does work in a card visual but in a table crashes the report as there are over 150k checks in total.  

I am thinking that if there was some way to create a table with this info it might work quicker maybe, but I am at a loss.  It doesnt seem like it should be hard but I am totally stuck now.  Any suggestions would be massively appreciated. 

CaseworkerTaskPass/FailIDCaseworker Signed OffCheck Date and Time

Neil	Opening	Fail	1		01/06/2023 15:00
Barry	Closure	Fail	2		02/06/2023 15:00
Neil	Closure	Pass	3		03/06/2023 15:00
Tony	Closure	Pass	4		04/06/2023 15:00
Neil	Customer Service	Fail	5		05/06/2023 15:00
Barry	Customer Service	Pass	6		06/06/2023 15:00
Barry	Closure	Pass	7		07/06/2023 15:00
Barry	Closure	Pass	8		08/06/2023 15:00
Barry	Closure	Pass	9	Yes	09/06/2023 15:00
Neil	Customer Service	Fail	10		10/06/2023 15:00
Tony	Customer Service	Pass	11		11/06/2023 15:00
Tony	Opening	Pass	12		12/06/2023 15:00
Neil	Closure	Fail	13		13/06/2023 15:00
Barry	Closure	Pass	14		14/06/2023 15:00
Tony	Customer Service	Pass	15		15/06/2023 15:00

[barrymcguigan84]

Managed to resolve it, the dax meausre above worked but it was also causing my report to crash when put into a table visual.  In the end I've used power query to create multiple tables to get the max values per task and then merged with the orginal table

[Ruthfoster5]

Hello @barrymcguigan84 ,

I am not an expert but I just want to say that To count the number of checks a caseworker has done up to and including the sign off, you can use Power Query or DAX in Power BI. Here's an approach using Power Query.

Load the data into Power Query. Create a new column called "Check Count" using the following steps. Select the "Caseworker" and "Check Date and Time" columns. Sort the table by "Caseworker" and "Check Date and Time" in ascending order. Add an index column starting from 1. Group the table by "Caseworker" and "ID" and create a new column called "Max Sign Off Index" using the formula List.Max([Index]). Expand the grouped table to include the "Max Sign Off Index" column. Create a custom column called "Check Count" using the formula if [Index] <= [Max Sign Off Index] then 1 else 0. Summarize the table by "Caseworker" and calculate the sum of the "Check Count" column. This approach will give you the count of checks up to and including the sign off for each caseworker. If you prefer to use DAX, you can create a measure using the following formula.

Check Count Up to Sign Off =
VAR MaxSignOffDate =
    MAX ( 'Checks to sign off'[Earliest Signed Off Date & Time] )
RETURN
    CALCULATE (
        COUNT ( 'Quality Checks'[ID] ),
        'Quality Checks'[Check Date and Time] <= MaxSignOffDate
    )

You can then use this measure in your table visual to display the count of checks up to sign off for each caseworker.

[barrymcguigan84]

This is the sort of thing I am after, but when I try and do the Max Aign Off Index column I get error in all the rows of the new column, 

[v-yueyunzh-msft · Accepted solution]

Hi , @barrymcguigan84 

According to your description, you want to get the count od the [ID] when the caseworker's [Signed Off]="Yes" and before the date and the [Pass/Fail]="Pass".

Here are the steps you can refer to :
(1)My test data is the same as your provided.
(2)We can create a measure like this:

Measure = var _signedoff_id = CALCULATE( MAX('Table'[ID]) , 'Table'[Signed  Off] = "Yes")
var _count = CALCULATE( COUNT('Table'[ID]) , 'Table'[ID]<=_signedoff_id , 'Table'[Pass/Fail]="Pass")
return
_count+0

Then we can get this:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly