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
Jorgast,
What is the result do you want to get based on the above sample data? Could you please post expected result in table format?
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