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 IIMy ultimate goal is to be able to see those customers where the orginating sales agent and final sales agent are different. I would like to filter out those customers where the 1st sales agent and the last sales agent are the same.
| Customer Name | Sale Agent Name | Sale Amount | Adjusted Sale Amount | Date of sale | Comment |
| Falcon | David | 375 | 375 | 11/1/2017 | Originating Sales Rep is different from final |
| Falcon | Matt | 375 | 450 | 11/11/2017 | Originating Sales Rep is different from final |
| Hawkeye | Carl | 350 | 350 | 11/1/2017 | Originating Sales Rep is different from final |
| Hawkeye | Matt | 350 | 325 | 11/10/2017 | Originating Sales Rep is different from final |
| Hulk | Bob | 200 | 200 | 11/1/2017 | Originating Sales Rep is different from final |
| Hulk | Bob | 200 | 225 | 11/5/2017 | Originating Sales Rep is different from final |
| Hulk | John | 225 | 300 | 11/5/2017 | Originating Sales Rep is different from final |
| IronMan | Mary | 275 | 275 | 11/1/2017 | Originating Sales Rep is different from final |
| IronMan | Mary | 275 | 300 | 11/6/2017 | Originating Sales Rep is different from final |
| IronMan | Matt | 300 | 400 | 11/12/2017 | Originating Sales Rep is different from final |
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
- Jorgast
Anonymous
This is better than what i had i was thinking. Thanks
- Jorgast
Anonymous
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