The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hello Community,
I have Table 1 and the goal is to create a column of the Minimum [TransDateTime] for when the Service Order Line went into a "completed stage" in the "NewValue" column. The "completed stages" in the newvalue column would be either "PaperworkReview","WaitingforPaperwork", or "Finished".
Service order may not directly go in sequence. It may go directly into "Finished" and skip the paperworkreview or waitingforpaperwork value.
Table 1
ServiceOrderLineId | ID | ServiceOrderId | ServiceOrderLineNum | TransDateTime | RecId | OldValue | NewValue |
85 | 28319952 | 51987677 | 4 | 7/18/21 1:12 | 544544 | PaperworkReview | Finished |
85 | 28319953 | 51987677 | 4 | 7/17/21 1:21 | 326442 | WaitingforPaperwork | PaperworkReview |
85 | 28319954 | 51987677 | 4 | 7/15/21 5:41 | 234997 | InProcess | WaitingforPaperwork |
32 | 28319955 | 51783544 | 2 | 7/15/21 3:33 | 234991 | PaperworkReview | Finished |
32 | 28319956 | 51783544 | 2 | 7/15/21 3:01 | 234999 | InProcess | PaperworkReview |
Thank you for review!
=var _max=calculate(max(table1[transdatetime]),allexcept(table1,table1[serviceorderlineid]) var _min=calculate(min(table1[transdatetime]),allexcept(table1,table1[serviceorderlineid]) return if(max(table1[transdatetime])=_max,"finished",if(max(table1[transdatetime])=_min,"paperworkreview","waitingforpaperwork"))
User | Count |
---|---|
15 | |
8 | |
6 | |
6 | |
5 |
User | Count |
---|---|
25 | |
13 | |
13 | |
8 | |
8 |