Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Problem
New Column Aggregate ID should return the Index ID where the User matches and Paid Date is between the First and Last Service dates of an Index ID. The Index ID marks a major event with Aggregate ID marking other services.
Solution I created in the End
1) Create a table with deduplicate Index ID and Paid amounts, add a column flagging the highest paid
2) Query against it
Aggregate ID = maxx (filter(aggregateIdTable, transactionTable[User] = aggregateIdTable[User] && transactionTable[Paid Date] >= aggregateIdTable[First Service] && transactionTable[Paid Date] <= aggregateIdTable[Last Service] && aggregateIdTable[Is Paid Amount Max?] = "Yes"), aggregateIdTable[Index Id])
Data
User | Store | First Service | Last Service | Paid Date | Paid | Index ID | Aggregate ID |
A67801 | B | 9/1/2023 | 9/1/2023 | 9/1/2023 | 118.0 | ||
A67801 | C | 9/2/2023 | 9/2/2023 | 9/2/2023 | 177.0 | ||
A67801 | A | 9/1/2023 | 9/2/2023 | 9/2/2023 | 153.0 | A67801-A-9/1/2023-9/2/2023 | |
C94823 | A | 9/4/2023 | 9/4/2023 | 9/4/2023 | 189.0 | A67801-A-9/4/2023-9/4/2023 |
Solved! Go to Solution.
Hi, @davidap
You can try the following methods.
Aggregate ID =
CALCULATE ( MAX ( 'Table'[Index ID] ),
FILTER (ALLEXCEPT( 'Table','Table'[User]),
[Paid Date] >= 'Table'[First Service]
&& [Paid Date] <= 'Table'[Last Service]
)
)
Is this the result you expect? Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks all. I built a solution last night that put a deduplicated list User, First/Last Service Dates, and the Index IDs (minus blanks) into another table then did CALCULATE+FILTER to pull in the value in the User and Paid dates were within spans. I also assigned Index IDs to those with the highest $ on those dates, in case multiple indices were found.
Hi,
Try this calculated column formula
Column = CALCULATE(MAX(Data[Index ID]),FILTER(Data,Data[User]=EARLIER(Data[User])&&Data[First Service ]<=EARLIER(Data[Paid Date ])&&Data[Last Service ]>=EARLIER(Data[Paid Date ])))
Hope this helps.
Hi, @davidap
You can try the following methods.
Aggregate ID =
CALCULATE ( MAX ( 'Table'[Index ID] ),
FILTER (ALLEXCEPT( 'Table','Table'[User]),
[Paid Date] >= 'Table'[First Service]
&& [Paid Date] <= 'Table'[Last Service]
)
)
Is this the result you expect? Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This was closest to what I used in the end. I added a column determine whether the Index had the max $ amount to break ties.
what's the expected output based on the sample data?
Proud to be a Super User!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |