Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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) )
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
64 | |
55 | |
53 | |
36 | |
34 |
User | Count |
---|---|
85 | |
73 | |
55 | |
45 | |
43 |