cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DerekC
Frequent Visitor

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:

DerekC_0-1627331938028.png

If all the columns in yellow match, then I want to look at each of the next columns and show what changed.  

DerekC_1-1627332092421.png

 The column in blue is what I would like to see.  

 

Any help would be greatly appreciated!
 

1 ACCEPTED SOLUTION
ryan_mayu
Super User II
Super User II

@DerekC 

1. create an index column in pq

1.PNG

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
DerekC
Frequent Visitor

Thank you!  I created a column that concatenated all the columns together that had to match first and instead of listing out all 6 time after time.  

@DerekC 

that's a good approach. you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ryan_mayu
Super User II
Super User II

@DerekC 

1. create an index column in pq

1.PNG

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors