Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
jakeudy
Helper I
Helper I

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!

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

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

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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:

Annotation 2020-06-11 103530.png

For the related .pbix file,pls click here.

 

 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
HotChilli
Super User
Super User

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.