Group lines in table based on values in certain fields

[jakeudy]

I have a dataset that looks like this:

ID	$ Amount	Delivery City	Delivery State	Name	Created Date	Sales Rep	Created By	Customer Account
57982	$100,000	Chicago	IL	Bob	3/1/2020	22	18	503
46396	$100,200	Chicago	IL	Henry	3/3/2020	31	56	247
25975	$100,000	Chicago	IL	Frank	3/5/2020	49	11	846
49684	$500,000	Dallas	TX	George	5/11/2020	14	20	104
09374	$510,000	Dallas	TX	Bill	5/12/2020	28	41	235
59607	$505,000	Dallas	TX	Jeff	5/8/2020	64	38	345
20348	$2,000	Phoenix	AZ	Dave	1/15/2020	77	61	489
49650	$1,900	Phoenix	AZ	Jim	1/11/2020	89	23	607
12345	$1,975	Phoenix	AZ	Mark	1/18/2020	94	14	783

The first column is all unique values. Basically I want to set up a custom column with logic that says "if the $ amounts are within a certain amount of each other (we'll say 10,000), the delivery city and state are the same, and the created dates are within a certain amount of time from each other (we'll say 2 weeks) then assign those rows the same ID. So ideally I would want my custom column to assign the same ID for the first three rows, another ID for the second 3 rows, and another ID for the last 3 rows. How would I go about doing this? Any help would be greatly appreciated!

[Anonymous]

Hi @jakeudy ,

Create a measure as below:

Measure = 
         VAR _amountpre = CALCULATE(MAX('Table'[$ Amount]),FILTER('Table','Table'[Index]=MAX('Table'[Index])-1))
         VAR _amountaft = CALCULATE(MAX('Table'[$ Amount]),FILTER('Table','Table'[Index]=MAX('Table'[Index])+1))
         VAR _cDatepre = CALCULATE(MAX('Table'[Created Date]),FILTER('Table','Table'[Index]=MAX('Table'[Index])-1))
         VAR _cDateaft = CALCULATE(MAX('Table'[Created Date]),FILTER('Table','Table'[Index]=MAX('Table'[Index])+1))
         
RETURN
   IF(ABS(_amountpre-MAX('Table'[$ Amount]))<10000||ABS(_amountaft-MAX('Table'[$ Amount]))<10000&&ABS(DATEDIFF(_cDatepre,MAX('Table'[Created Date]),DAY))<14||ABS(DATEDIFF(_cDateaft,MAX('Table'[Created Date]),DAY))<14,CALCULATE(MIN('Table'[ID]),FILTER(ALL('Table'),'Table'[Delivery City]=MAX('Table'[Delivery City])&&'Table'[Delivery State]=MAX('Table'[Delivery State]))),MAX('Table'[ID]))

Finally you will see:

For the related .pbix file,pls click here.

 
Best Regards,
Kelly

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

[HotChilli · Accepted solution]

I have only given this a brief test but see how you get on

ColAdd = VAR _city = TableQ[Delivery City]
         VAR _state = TableQ[Delivery State]
         VAR _amount = TableQ[$ Amount]
         VAR _cDate = TableQ[Created Date]
RETURN
   CALCULATE(MIN(TableQ[ID]), 
             FILTER(TableQ, TableQ[Delivery City] = _city &&
                            TableQ[Delivery State] = _state && 
                            ABS(TableQ[$ Amount] - _amount) < 10001 &&
                            ABS(DATEDIFF(TableQ[Created Date], _cDate, DAY)) < 14) )