Forum Discussion

jakeudy's avatar
jakeudy
Helper I
6 years ago
Solved

Group lines in table based on values in certain fields

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