Forum Discussion

grojnak's avatar
grojnak
New Member
2 years ago
Solved

Count changes in a field (with date)

Hello.

 

I have a table with these fields:

- employee: ID number of the worker

- location: "home" or "office"

- date: each day the employee worked

 

I need to count how many changes from "home" to "office" or "office" to "home" had each employee. Of course, each change should be coherent in time. For example, for one employee:

dateLocation
25/09home
26/09office
27/09home
28/09home
29/09home

 

the count should be 2. How can I do that? I may also need to detail only the changes. Which is the best approach? A new column with M? A new table? I discard the measure, because of that need of the detailed info of only the changes.

 

Thanks,

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi grojnak ,

     

    I suggest you to try code as below to create a measure.

    My Sample:

    Count Location Change For Employee =
VAR _SUMMARIZE =
    SUMMARIZE (
        ADDCOLUMNS (
            'Table',
            "CheckPoint",
                CALCULATE (
                    MIN ( 'Table'[date] ),
                    FILTER (
                        ALLEXCEPT ( 'Table', 'Table'[Employee ID] ),
                        'Table'[date] > EARLIER ( [date] )
                            && 'Table'[Location] <> EARLIER ( [Location] )
                    )
                )
        ),
        [Employee ID],
        [CheckPoint]
    )
RETURN
    COUNTX ( _SUMMARIZE, [CheckPoint] )

    Result is as below.

     

    Best Regards,

    Rico Zhou

     

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

     

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Yes, this can easily be done in Power Query via grouping.  Read about GroupKind.Local

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi grojnak ,

     

    I suggest you to try code as below to create a measure.

    My Sample:

    Count Location Change For Employee =
VAR _SUMMARIZE =
    SUMMARIZE (
        ADDCOLUMNS (
            'Table',
            "CheckPoint",
                CALCULATE (
                    MIN ( 'Table'[date] ),
                    FILTER (
                        ALLEXCEPT ( 'Table', 'Table'[Employee ID] ),
                        'Table'[date] > EARLIER ( [date] )
                            && 'Table'[Location] <> EARLIER ( [Location] )
                    )
                )
        ),
        [Employee ID],
        [CheckPoint]
    )
RETURN
    COUNTX ( _SUMMARIZE, [CheckPoint] )

    Result is as below.

     

    Best Regards,

    Rico Zhou

     

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