The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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:
date | Location |
25/09 | home |
26/09 | office |
27/09 | home |
28/09 | home |
29/09 | home |
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,
Solved! Go to Solution.
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.
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.
Yes, this can easily be done in Power Query via grouping. Read about GroupKind.Local
User | Count |
---|---|
5 | |
3 | |
3 | |
2 | |
1 |
User | Count |
---|---|
11 | |
7 | |
5 | |
5 | |
4 |