Forum Discussion

DerekC's avatar
DerekC
Frequent Visitor
5 years ago
Solved

What changed about the Rows if Multiple columns Match

I have multiple contracts entered into a database and if I match on 6 columns then I want to know what changed from the previous record to the new record.  Example below: If all the columns...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    DerekC 

    1. create an index column in pq

    2. create a column

    change = 
VAR _last=MAXX(FILTER('Table','Table'[Origin]=EARLIER('Table'[Origin])&&'Table'[Destination]=EARLIER('Table'[Destination])&&'Table'[Carrier]=EARLIER('Table'[Carrier])&&'Table'[Contract No]=EARLIER('Table'[Contract No])&&'Table'[Condition]=EARLIER('Table'[Condition])&&'Table'[Type]=EARLIER('Table'[Type])&&'Table'[Index]<EARLIER('Table'[Index])),'Table'[Index])
VAR _startdate=MAXX(FILTER('Table','Table'[Origin]=EARLIER('Table'[Origin])&&'Table'[Destination]=EARLIER('Table'[Destination])&&'Table'[Carrier]=EARLIER('Table'[Carrier])&&'Table'[Contract No]=EARLIER('Table'[Contract No])&&'Table'[Condition]=EARLIER('Table'[Condition])&&'Table'[Type]=EARLIER('Table'[Type])&&'Table'[Index]=_last),'Table'[Start Date])
VAR _start=if('Table'[Start Date]=_startdate,blank(),"Start Date")
VAR _enddate=MAXX(FILTER('Table','Table'[Origin]=EARLIER('Table'[Origin])&&'Table'[Destination]=EARLIER('Table'[Destination])&&'Table'[Carrier]=EARLIER('Table'[Carrier])&&'Table'[Contract No]=EARLIER('Table'[Contract No])&&'Table'[Condition]=EARLIER('Table'[Condition])&&'Table'[Type]=EARLIER('Table'[Type])&&'Table'[Index]=_last),'Table'[End Date])
VAR _end=if(_enddate='Table'[End Date],blank(),if(ISBLANK(_start),"End Date",", End Date"))
VAR _rate=MAXX(FILTER('Table','Table'[Origin]=EARLIER('Table'[Origin])&&'Table'[Destination]=EARLIER('Table'[Destination])&&'Table'[Carrier]=EARLIER('Table'[Carrier])&&'Table'[Contract No]=EARLIER('Table'[Contract No])&&'Table'[Condition]=EARLIER('Table'[Condition])&&'Table'[Type]=EARLIER('Table'[Type])&&'Table'[Index]=_last),'Table'[Rate])
VAR _rate2=if('Table'[Rate]=_rate,blank(),if(ISBLANK(_start)&&ISBLANK(_end),"Rate",", Rate"))
return  if(ISBLANK(_last),BLANK(),_start&_end&_rate2)

    pls see the attachment below

ryan_mayu's avatar
ryan_mayu
Super User
5 years ago

DerekC 

1. create an index column in pq

2. create a column

change = 
VAR _last=MAXX(FILTER('Table','Table'[Origin]=EARLIER('Table'[Origin])&&'Table'[Destination]=EARLIER('Table'[Destination])&&'Table'[Carrier]=EARLIER('Table'[Carrier])&&'Table'[Contract No]=EARLIER('Table'[Contract No])&&'Table'[Condition]=EARLIER('Table'[Condition])&&'Table'[Type]=EARLIER('Table'[Type])&&'Table'[Index]<EARLIER('Table'[Index])),'Table'[Index])
VAR _startdate=MAXX(FILTER('Table','Table'[Origin]=EARLIER('Table'[Origin])&&'Table'[Destination]=EARLIER('Table'[Destination])&&'Table'[Carrier]=EARLIER('Table'[Carrier])&&'Table'[Contract No]=EARLIER('Table'[Contract No])&&'Table'[Condition]=EARLIER('Table'[Condition])&&'Table'[Type]=EARLIER('Table'[Type])&&'Table'[Index]=_last),'Table'[Start Date])
VAR _start=if('Table'[Start Date]=_startdate,blank(),"Start Date")
VAR _enddate=MAXX(FILTER('Table','Table'[Origin]=EARLIER('Table'[Origin])&&'Table'[Destination]=EARLIER('Table'[Destination])&&'Table'[Carrier]=EARLIER('Table'[Carrier])&&'Table'[Contract No]=EARLIER('Table'[Contract No])&&'Table'[Condition]=EARLIER('Table'[Condition])&&'Table'[Type]=EARLIER('Table'[Type])&&'Table'[Index]=_last),'Table'[End Date])
VAR _end=if(_enddate='Table'[End Date],blank(),if(ISBLANK(_start),"End Date",", End Date"))
VAR _rate=MAXX(FILTER('Table','Table'[Origin]=EARLIER('Table'[Origin])&&'Table'[Destination]=EARLIER('Table'[Destination])&&'Table'[Carrier]=EARLIER('Table'[Carrier])&&'Table'[Contract No]=EARLIER('Table'[Contract No])&&'Table'[Condition]=EARLIER('Table'[Condition])&&'Table'[Type]=EARLIER('Table'[Type])&&'Table'[Index]=_last),'Table'[Rate])
VAR _rate2=if('Table'[Rate]=_rate,blank(),if(ISBLANK(_start)&&ISBLANK(_end),"Rate",", Rate"))
return  if(ISBLANK(_last),BLANK(),_start&_end&_rate2)

pls see the attachment below