Count the number of value changes in a column for a group of rows (Sample file attached)

[StuartSmith]

I have a table that records when someone is off ill and whether the absent day is backfilled.  As a requirement of the backfill, a backfill employee can only be rotated 1 and if a backfill is rotated more than once it should be flagged up.  Is there a way to count the number of times a backfill value is changed.  Each absence period (to/from) has its own unique ID.

As an example, Absence ID 1, has 3 changes.  Obviously, I can use "DistinctCount" to count the number of Backfill staff, but i need to be able to count the number of times the value changes over the period of absence.  Hope this makes sense & thanks in advance.

Backfill Changes PBIX File 

[StuartSmith]

Thanks, that worked great.

[v-zhangti · Accepted solution]

Hi, @StuartSmith 

You can try the following methods.

Column:

Change =
VAR _N1 =
    MAXX ( FILTER ( 'Sheet1',
            [AdsenceDate] = EARLIER ( 'Sheet1'[AdsenceDate] ) - 1
                && [AbsenceID] = EARLIER ( Sheet1[AbsenceID] )
        ),
        [Backfill]
    )
RETURN
    IF ( [Backfill] = _N1, BLANK (), _N1 )

Count = CALCULATE(COUNT(Sheet1[Change]),FILTER(Sheet1,[AbsenceID]=EARLIER(Sheet1[AbsenceID])))+0

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[StuartSmith]

Thanks for your help, but I've noticed that it takes into account empty cells.  As an example, if the first cell is empty and then the second cell has a name, thats classed as 1 change or if the first cell is populated, the second empty and the third populated it counts that as 2 changes.

Is there a way to ignore empty cells and only count cells that have values in?

Thanks in advance.