Bradford factor Calculation

Thanks for that Simon.

Here is my suggested approach - uploaded with simplified data here:

https://www.dropbox.com/s/gt1jytqobrc9ilt/Bradford%20Factor.pbix?dl=0

1. Add a Block Index column to your original table, so that Block Index has a different value for each block of consecutive days with the same value of Abs, across all ClockNos.
   To do this, I carried out a series of steps in the Query Editor:
   
   1. Sort the rows by ClockNo & Date
   2. Group the rows by ClockNo & Abs, using GroupKind.Local, which groups each consecutive block of ClockNo & Abs values.
   3. Add a Block Index column to the resulting grouped table.
   4. Re-expand the grouped rows, then tidy up.
2. Create a Separate Absences measure, which is a DISTINCTCOUNT of Block Index where Abs="2".
3. Create a Days Absent measure, which counts rows of the table where Abs="2".
4. Create a Bradford Factor measure equal to [Separate Absences] ^ 2 * [Days Absent]

The measures in the end are:

Separate Absences = 
CALCULATE (
    DISTINCTCOUNT ( 'Bradford Factor'[Block Index] ),
    'Bradford Factor'[Abs] = "2"
)

Days Absent = 
CALCULATE (
    COUNTROWS ( 'Bradford Factor' ),
    'Bradford Factor'[Abs] = "2"
)

Bradford Factor = 
IF (
    HASONEVALUE ( 'Bradford Factor'[ClockNo] ),
    // Only calculate for one ClockNo at a time, and don't aggregate ClockNos
    [Separate Absences]
        ^ 2
        * [Days Absent]
)

Bradford Factor here doesn't aggregate ClockNos, but you could aggregate using AVERAGEX or some other method if you want.

Hopefully this does what you expect and can be applied to your data model.

Cheers,

Owen