Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 in yellow match, then I want to look at each of the next columns and show what changed.
The column in blue is what I would like to see.
Any help would be greatly appreciated!
Solved! Go to Solution.
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
Proud to be a Super User!
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.
that's a good approach. you are welcome
Proud to be a Super User!
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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |