Forum Discussion
Resolver IIFinding Max value in a group
Jorgast，
Create the following columns in your table.Original agent = CALCULATE(FIRSTNONBLANK(Table[Sale Agent Name],""), FILTER(Table, Table[Customer Name]=EARLIER(Table[Customer Name]) && Table[Date of sale]<EARLIER(Table[Date of sale])))
checkcol = IF(Table[Original agent]<>BLANK() && Table[Sale Agent Name]<>Table[Original agent],1,0)
Then set the value of checkcol to 1 in visual level filters.
Regards,
Lydia
Resolver IIAnonymous
This is better than what i had i was thinking. Thanks
Resolver IIAnonymous
Would the code still work when the data is sorted in a different manner?
When i try using the First Non blank, it seems to be looking at the first non blank record in the data set, so everything is being compared to that.
Original agent = CALCULATE(FIRSTNONBLANK(Table[Sale Agent Name],""), FILTER(Table, Table[Customer Name]=EARLIER(Table[Customer Name]) && Table[Date of sale]<EARLIER(Table[Date of sale])))
Customer NameSale Agent NameSale AmountAdjusted Sale AmountDate of saleComment
| Falcon | David | 375 | 375 | 11/1/2017 | Originating Sales Rep is different from final |
| Hawkeye | Carl | 350 | 350 | 11/1/2017 | Originating Sales Rep is different from final |
| Hulk | Bob | 200 | 200 | 11/1/2017 | Originating Sales Rep is different from final |
| IronMan | Mary | 275 | 275 | 11/1/2017 | Originating Sales Rep is different from final |
| Hulk | Bob | 200 | 225 | 11/5/2017 | |
| Hulk | John | 225 | 300 | 11/5/2017 | Originating Sales Rep is different from final |
| IronMan | Mary | 275 | 300 | 11/6/2017 | Originating Sales Rep is different from final |
| Hawkeye | Matt | 350 | 325 | 11/10/2017 | Originating Sales Rep is different from final |
| Falcon | Matt | 375 | 450 | 11/11/2017 | Originating Sales Rep is different from final |
| IronMan | Matt | 300 | 400 | 11/12/2017 | Originating Sales Rep is different from final |