Forum Discussion
Detect Change in value
I have a dataset with many rows that I need to clean. The data consists of PersonTripID as unique identifier for every person's every trip location. Person is identified as PersonID. For a given PersonID, I wish to detect change in location of the person.This helps me remove samelocations and obtain a unique trip for every person.
Initial dataset:
| PersonLocationID | PersonID | Location | Date | Count |
| 1001 | 1 | San Diego | 1/1/2018 | 1 |
| 1002 | 1 | LA | 1/2/2018 | 1 |
| 1003 | 1 | LA | 1/3/2018 | 1 |
| 1004 | 1 | Las Vegas | 1/4/2018 | 1 |
| 1005 | 2 | NYC | 1/5/2018 | 1 |
| 1006 | 2 | Niagara Falls | 1/6/2018 | 1 |
| 1007 | 2 | Boston | 1/7/2018 | 1 |
| 1008 | 5 | Orlando | 1/8/2018 | 1 |
| 1009 | 5 | Miami | 1/9/2018 | 1 |
| 1010 | 5 | Miami | 1/10/2018 | 1 |
| 1011 | 5 | KeyWest | 1/11/2018 | 1 |
| 1012 | 5 | Miami | 1/12/2018 | 1 |
| 1013 | 5 | Orlando | 1/13/2018 | 1 |
| 1014 | 5 | Orlando | 1/14/2018 | 1 |
| 1015 | 3 | Houton | 1/15/2018 | 1 |
| 1016 | 3 | Austin | 1/16/2018 | 1 |
| 1017 | 3 | Houston | 1/17/2018 | 1 |
Final Dataset:
| PersonLocationID | PersonID | Location | Date | Count | Change |
| 1001 | 1 | San Diego | 1/1/2018 | 1 | 1 |
| 1002 | 1 | LA | 1/2/2018 | 1 | 1 |
| 1003 | 1 | LA | 1/3/2018 | 1 | 0 |
| 1004 | 1 | Las Vegas | 1/4/2018 | 1 | 1 |
| 1005 | 2 | NYC | 1/5/2018 | 1 | 1 |
| 1006 | 2 | Niagara Falls | 1/6/2018 | 1 | 1 |
| 1007 | 2 | Boston | 1/7/2018 | 1 | 1 |
| 1008 | 5 | Orlando | 1/8/2018 | 1 | 1 |
| 1009 | 5 | Miami | 1/9/2018 | 1 | 1 |
| 1010 | 5 | Miami | 1/10/2018 | 1 | 0 |
| 1011 | 5 | KeyWest | 1/11/2018 | 1 | 1 |
| 1012 | 5 | Miami | 1/12/2018 | 1 | 1 |
| 1013 | 5 | Orlando | 1/13/2018 | 1 | 1 |
| 1014 | 5 | Orlando | 1/14/2018 | 1 | 0 |
| 1015 | 3 | Houton | 1/15/2018 | 1 | 1 |
| 1016 | 3 | Austin | 1/16/2018 | 1 | 1 |
| 1017 | 3 | Houston | 1/17/2018 | 1 | 1 |
I was trying like this:
change=if(location<>earlier(location),1,0)
Thanks,
Milay
1 Reply
Hey Anonymous
To make it easy on yourself I would create a helper column that CONCATENATES (https://docs.microsoft.com/en-us/dax/concatenate-function-dax) your Person ID and Location. Then view the pbix in the solution in the thread linked below as I believe it has your answer in it:
https://community.powerbi.com/t5/Desktop/Change-in-Values-over-Time-Calculated-Column/td-p/498913
If this helps please kudo.
If this solves your problem please accept it as a solution.