Forum Discussion

jakeudy's avatar
jakeudy
Icon for Helper I rankHelper I
6 years ago
Solved

Group lines in table based on values in certain fields

I have a dataset that looks like this:

ID$ AmountDelivery CityDelivery StateNameCreated DateSales RepCreated ByCustomer Account
57982$100,000ChicagoILBob3/1/20202218503
46396$100,200ChicagoILHenry3/3/20203156247
25975$100,000ChicagoILFrank3/5/20204911846
49684$500,000DallasTXGeorge5/11/20201420104
09374$510,000DallasTXBill5/12/20202841235
59607$505,000DallasTXJeff5/8/20206438345
20348$2,000PhoenixAZDave1/15/20207761489
49650$1,900PhoenixAZJim1/11/20208923607
12345$1,975PhoenixAZMark1/18/20209414783

 

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!

Need Help
  • HotChilli's avatar
    HotChilli
    6 years ago

    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) )

2 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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) )
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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!