Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have one or more orders which are in the future, and I would like to add a column to the table of orders with information from previous similar orders which have been done as an indication on what to excpect. i.e. travel time.
An order in the order table has the following columns: See example table in the bottom.
Explanation:
What I have manage so far thanks to the forum:
Explanation | Unique order no | Unique Customer no | What type of order | Travel time | Status of order |
SQL Name Table and Column | 'HM Ordre'[VB Order no] | 'HM Ordre'[KundeNr] | 'HM Ordre'[OrdreType] | 'HM Ordre'[Actual_Travel_Hours_HM] | 'HM Ordre'[Status] |
Example order in future | 79315 | 2575 | 12 |
| 0 |
Example order done | 60604 | 130 | 12 | 3,5 | 3 |
Solved! Go to Solution.
I found the solution myself:
Hi @Anonymous
Assume there is a column like [value id] in my table to identify the index of orders as in ascending order.
For example, for customer no=1, value id=2 is the feature order, value id=1 is the previous order
(they have the same value for [type of order] and [status])
value id | order no | customer no | type of order | travel time | status |
1 | 1 | 1 | 12 | 2 | 0 |
2 | 2 | 1 | 12 | 0 |
Then i can create measures
sum per customer = CALCULATE(SUM(Sheet1[travel time]),FILTER(ALLEXCEPT(Sheet1,Sheet1[customer no],Sheet1[type of order],Sheet1[status]),Sheet1[value id]<MAX(Sheet1[value id]))) feature id = CALCULATE(MAX(Sheet1[value id]),ALLEXCEPT(Sheet1,Sheet1[customer no],Sheet1[type of order],Sheet1[status])) count rows = CALCULATE(COUNT(Sheet1[value id]),ALLEXCEPT(Sheet1,Sheet1[customer no],Sheet1[type of order],Sheet1[status])) total time = IF(SUM(Sheet1[value id])<>[feature id]||[count rows]=1,0,[sum per customer])
final output = IF([count rows]>2,[total time]/([count rows]-1),[total time])
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your suggestion!
I see I need to clarify som parameters.
How would you solve it then?
I found the solution myself:
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |