Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to 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) )
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.
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) )
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
72 | |
67 | |
51 | |
38 | |
26 |
User | Count |
---|---|
89 | |
52 | |
45 | |
39 | |
38 |