Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Please advise how to solve this problem in power query. I have a data set consisting: Unique Order IDs, Operation Number, Work Center, Hours. The data is sorted by Order ID, then operation number in ascending order. For example,
Order ID, Op Number, Work Center, Hours
111, 10, AA, 1.1
111, 20, AA, 1.2
111, 30, AA, 1.3
111, 40, BB, 1.4
111, 50, BB, 1.5
111, 60, AA, 1.6
111, 70, AA, 1.7
222, 10, AA, 1.8
222, 20, BB, 1.9
I want a result table that sums up the hours when the next Operation is for the same Work Center:
111, 10, AA, 3.6 (=1.1+1.2+1.3)
111, 40, BB, 2.9 (=1.4+1.5)
111, 60, AA, 3.3 (=1.6+1.7)
222, 10, AA, 1.8
222, 20, BB, 1.9
Thank you.
Solved! Go to Solution.
then you just need an index column and use DAX to create a new table
Table 2 =
VAR tbl=ADDCOLUMNS('Table',"check",var _lastorder=maxx(FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1),'Table'[Order ID])
var _lastcenter=maxx(FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1),'Table'[ Work Center])
VAR _check=if(_lastorder<>'Table'[Order ID]||_lastcenter<>'Table'[ Work Center],"Yes")
return _check)
var tbl2=ADDCOLUMNS(tbl,"opnumber",if([check]="Yes",'Table'[ Op Number],maxx(FILTER(tbl,([check])="Yes"&&'Table'[Index]<EARLIER('Table'[Index])),'Table'[ Op Number])))
return SUMMARIZE(tbl2,'Table'[Order ID],[opnumber],'Table'[ Work Center],"hours",sum('Table'[ Hours]))
pls see the attachment below
Proud to be a Super User!
here is a workaround for you. pls see the attachment below
Proud to be a Super User!
Great, nicely done. Instead of having the table as a visual, is it possible to have the results in a new data table instead?
then you just need an index column and use DAX to create a new table
Table 2 =
VAR tbl=ADDCOLUMNS('Table',"check",var _lastorder=maxx(FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1),'Table'[Order ID])
var _lastcenter=maxx(FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1),'Table'[ Work Center])
VAR _check=if(_lastorder<>'Table'[Order ID]||_lastcenter<>'Table'[ Work Center],"Yes")
return _check)
var tbl2=ADDCOLUMNS(tbl,"opnumber",if([check]="Yes",'Table'[ Op Number],maxx(FILTER(tbl,([check])="Yes"&&'Table'[Index]<EARLIER('Table'[Index])),'Table'[ Op Number])))
return SUMMARIZE(tbl2,'Table'[Order ID],[opnumber],'Table'[ Work Center],"hours",sum('Table'[ Hours]))
pls see the attachment below
Proud to be a Super User!
Thanks for the solution. However, my dataset has 200,000+ rows and the application hangs while computing Column 2. Is there a more optimal solution? In additional, do you have a solution in Power Query?
i don't know how to do this in PQ. Since you have accepted the solution, i suggest you to post a new subject that will have a new solution quickly.
Proud to be a Super User!
I managed to overcome the issue by modifying Column 2, by adding another AND condition to filter the search to a smaller list of same Order ID:
From
if([check]="Yes",'Table'[ Op Number],maxx(FILTER(tbl,([check])="Yes"&&'Table'[Index]<EARLIER('Table'[Index])),'Table'[ Op Number]
To
if([check]="Yes",'Table'[ Op Number],maxx(FILTER(tbl,([check])="Yes"&&'Table'[Order ID]=EARLIER('Table'[Order ID])&&'Table'[Index]<EARLIER('Table'[Index])),'Table'[ Op Number]
glad to hear that.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |