Forum Discussion

barrymcguigan84's avatar
barrymcguigan84
Frequent Visitor
3 years ago
Solved

Count Quality Checks Up to Sign Off

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 answe...
Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    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

     

     

Ruthfoster5's avatar
Ruthfoster5
New Member
3 years ago

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's avatar
    barrymcguigan84
    Frequent Visitor
    3 years ago

    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,