Forum Discussion
Automate operator for each change in root table
Hi cprpontes ,
Please try the following to see if the problem above still occurs.
Calculate the maximum time and minimum time.
Max Time = CALCULATE ( MAX ( 'operators'[HR I/F] ), FILTER ( 'operators', 'operators'[OPERADOR] = EARLIER ( 'operators'[OPERADOR] ) && 'operators'[ESTAÇÃO] = EARLIER ( 'operators'[ESTAÇÃO] ) ) )Min Time = CALCULATE ( MIN ( 'operators'[HR I/F] ), FILTER ( 'operators', 'operators'[OPERADOR] = EARLIER ( 'operators'[OPERADOR] ) && 'operators'[ESTAÇÃO] = EARLIER ( 'operators'[ESTAÇÃO] ) ) )Then:
REV OPER = CALCULATE ( MAX ( 'operators'[OPERADOR] ), FILTER ( 'operators', 'operators'[Min Time] < EARLIER ( 'Failures'[HR I/F] ) && 'operators'[Max Time] > EARLIER ( 'Failures'[HR I/F] ) && 'operators'[ESTAÇÃO] = EARLIER ( 'Failures'[ESTAÇÃO] ) ) )A sample PBIX. for your reference is attached.
Hope it helps,
Community Support Team _ Caitlyn Yan
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi cprpontes ,
Please try this in query editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdfBasJQEIXhVylZCya3ub33ZhfElhRtirG4EN//NWqLaDwzkzmbkMUPIYdvM+dztR+PfbWq8rp5W4c6NNf39/54faaurl/6fXVZLUSla+7RtD18DKORBSZrmll3Gna7fr+d9DL8ld9EGbrXeziNm/Fw0L8dupbq2i7G5W4zwXoL2Xw/4zdunTvMf/e04OfPZrsbvtQPPw24FM73UxjcmtZt5rs9msSYS5w5kenmEm0OS9tcIs2JzjCXSHOJM5dIc9i5w3jmEmsuEeaw0cwlwlxmzGXOnMh0c5k2h6VtLpPmRGeYy6S5zJnLpDns3GE8c5k1lwlz2GjmMmGuMOYKZ05kurlCm8PSNldIc6IzzBXSXOHMFdIcdu4wnrnCmiuEOWw0c8U3F1rCHEaGOZmp5jCzzYnSNIelZU52ujnsLHOPbtEcZKY50bnDOOYwNM1BqJoTjWIOGt1cZMxFzpzIdHORNoelbS6S5kRnmIukuciZi6Q57NxhPHORNRcJc9ho5iJhjrlbMbLMcXcrZgvm6LsVS9McebdiZ5rj7tZA3q2ic4fxzLF3ayDuVtFo5oy79fIL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OPERADOR = _t, DATA = _t, ESTAÇÃO = _t, #"HR I/F" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"OPERADOR", type text}, {"DATA", type date}, {"ESTAÇÃO", type text}, {"HR I/F", type time}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"OPERADOR", "ESTAÇÃO"}, {{"MaxTime", each List.Max([#"HR I/F"]), type nullable time}, {"MinTime", each List.Min([#"HR I/F"]), type nullable time}, {"Table", each _, type table [OPERADOR=nullable text, DATA=nullable date, ESTAÇÃO=nullable text, #"HR I/F"=nullable time]}}),
#"Expanded Table" = Table.ExpandTableColumn(#"Grouped Rows", "Table", {"DATA", "HR I/F"}, {"Table.DATA", "Table.HR I/F"})
in
#"Expanded Table"
(currentESTACAO, currentHR) =>
let
source = Table.SelectRows(#"F277 REVEZAMENTO TESTE", each [ESTAÇÃO] = currentESTACAO and [MinTime] < currentHR and [MaxTime] > currentHR){0}[OPERADOR]
in
source
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pVvbbhtHEv0Vws8B0vfLvMmUEhkbawVJ3iwQBITWoR0itrRLS5tFvn67uoecGk5Vd8tGAj0E4UHd61RVzy+/vPrnD8r7oKRWSkghjJOvvnu1vk1/fvh5dX65vl7dbP/zvP3ylP5L+F7675VQ8L+4warByNXZ21e/fodhdIKRKoQqjEMwftBxCeNAGqniAWb9+/b9H6ur+4/7+9X67Paq4IQDjh+EGrRa4oBW0moDkpzdpb93SYjL87dvNpvzi5sTaRKK4aURQfVLYwajGWmk0lXjRATjBu0YmChUTSk/oUhJmsZlYUzVUwrZRqpBGhpGhawTJ4yyCMUlV9EqCaFjr0rJMJ71k6+hIC9pMSjCvBDCwsdjJqz32/un7ep2+/55v129u71e+En7wXAWNl7VgbB1jBmE5bJBTzbOAbh5/fz09PjwZXOz/bJ9WmhnxSCIBC1ChWMM3m0/PWyfVj89ftw9LOSxclCB8XkU4oBx9e7N+SJerB2M4H5r5df+NtUoX/9tKipcDmsX67+F0GKiU3p7tP/145/b/Wj/zd8/fFgEKJQ1LlmisY2IwBJFtsxCYatWkmMwhEGIQRJGyZXEmFrynqAYxrRSy6nIcuE5hVbCsqkwVU1dKbV+hqO4lPFS1sqSQSgQ64x9nBVHlJ92D8//q3W0BBVSb+SgtKsrhnBS2ZZE7uUGq42o40SEo0icLE+QstvQUpPVMhvauTDHud3ud/efVlfPn/+13S+sLS3ZSvRUvnvabBhUimrC99lERlb7o5uhWAZFGB26HaY0GdEFR/U7XtnEHxiHeZRl725fb96cQ3o9f3oSSxhPwhQj+6Nai/o/M7AfFNdDEI8hVcLJrkJqbd+uUmrYLD2Lpm5hHH8JhxJnVOtYe5hkx4ppSURg+ichJVNXmqyZYVBNNkdxonnVEo+NY1iyqISeSlijdyVPJVpEkNfSQHUtq7BlEopZaDVaRuQyWBEHm8fQ9LWwNOMq4uAqmFBYRhFjndnPpDEpKbiqrBrFAstjyb6eM0Jb1fAWBvJku8kCCdVgGjjXYaAjkmssplVOjaLQ6iQRx+9twz5+hmMX0VzCRzlkZ5pAIYclOshlV6JijaocZjiG7zWh1zxQfDhS7vqGqJh4/aCY+iWiNwedXn963j49Pj79vrpcn6+u949Juc8nBoowOXNtq1uxhKIHzRQNXdhKLd2xbnTbyhaKITSA3AyIqofZ8bpzRozA5ij2NJp6mld/3n3Ybc6u7jT8MQsjK3pbktNC1YuYRyjJyAzXlV5XVzfYMkAw2Ckn9s3gCSWSlDlbRilMvD//e7u/f0oJurl4+O2kgEW6hR6kCbZTmoRimHqh8WhS97ZW5Mw8UsFWVUYm1po0cfGUMFXjYFfpwA6zMprjMAvRt0puH3P88+7h4wIKOhcTybrwwSLSP3a/bR83ZYS7+fH1IkENTU+LSKI+T8zksSSZyx1QTKrVaU+CgRxlJm1npwViY/cSYV8iOZ5hVKPw4FCEpQmXXt71NdIIDZA1s5G2213Q/4jCXMq7rabXURopYMymFhplgeAbbTQioPQvu40MQjf8hUVKA7JlBlvh5JHSvT1bry7uLi9uri7uTnyVQJRMreubQTTBdcfQcRIXVKIUYpVSZV6me0p0yFGJejFZ4S0GcqkicqwQ9Zvr22tQdbV72J2u+AElsmtWvP5bn20W+Q1uloQMI4XTaJlWbZ05XAbBUXfZTbkLELdRmbmpUd0BqTLq29adYAYUyHX4uAmp7uXiHIZbhCR5WnvLmUCR7BKlKqM7Shsp9RuOMIsy4XTcmQDHk5S51GW0v2ps6RNSKqlUDSujgOqeuQDJk1NO2YWJifvUQ1vCQlWxPSd275sLEpeqKeFFJZBQzkoYBthFjQiNc9wMyFa8FnvXuwAU2FNaSpAqc8bywBaUm9ulbMgTMZDmi4gSR6D6aQ9wLEFYD3tr0bvLAiDHUk2tuvlY3jZXyqOYCNlu//nP+5Rjp2Rz5jYZSXuPgTRFdnXjly+X/GL+RTgwsHDKqcY6CmkGYQQE5pq+yOhq7qMAUEAaBIOTMta/QCJlgMMskMo609bmOcQZFCx703R5TfZrJXEJIVhMQEBwV5ULIGHy8FMnwFgtbaE40vI4NMBTDHguT94+k/YZF/PtE5FUeRG5sM+h6MvujSYgWVagxmkUfpzy3TPx51yLxWC7mJBMw8RNCMc1UmO7LyFDoR0ugMrJS9h65OCcsBaqBimRVra6Cp/ZCPozYeDCh+IUOq0ctQGGsKajSAKs4HabJlz616G1kT9Fsu5bMwrWhoLxt4rKdFoXYKiwKQdFV7u4yjnK0kUiORn6n3VVF2EYlwZ3LrenlwKL+W2mkAeKQWNo1bsZyVRFMjjK+U4OBmOtYnIAa0TGHPzYLELuaNXOaT9P1pxBeo2aC+a3+1caGP7oTI7VhTkqdYAiF/lz2H25+sFZzaVZtiQO5+3devXf7f7L7vFhCWSJFnlcnnavU2gcNc6PYjLPzfb9dpeE2dys3Ua7v13+tbS0JWrMoRv4apdEOVDYKNMkRa1SoQ4JHEuetlphdFFL9ZYYCuU4gTY3enEGZBm3A+l7gdvhut92+/Imj4WJSwsfbYN2RHVGDIsvxSVW/yFTZhq7LFeiPMVrjB8zefRguZYtQ8PGM4Hy5Mmw4cb7GxzJKj/hYniWra6vsDg6P5ukYYTpPYgmHMeSNYXmxVbzTjCGy08748LEYQJZRwfWOmmeNt3WiUSXOGjlelumyU8dOZTGimgGZFi+p7WpvrbDMHBKZyeF5us/0wQqywbU/RpWtsCsudJuaide7HK4gHMVQ5gqtzjKovK5mW0zpu91iRrfNDNJ3rjqz3ACQYkPZ8P6KRTplAZwKsfLcSRObmosTVV+CsSZWPr6a08/wRhJ2vgrYJbzyziAB9vamSD7GAWvT0n7iIi81XijkoEo/pUVC7bvps+gfA1pysM4K5CLfWfnMogzgyJU5S66o2C9vbTyYX/j619kIGdZQ6Z5CWbVOKji8IGH2Iuq88JZhB3l3dQ/u1DyHZ2uF153V2SdZ2iuIkvdfSXW46cmNJC3jZ41BY/Om3ZuH2Rt9wOwBBR5poKA6k/NNRx4lwyjhKE0rr5pn0qzziWei0NrXmAgSe0Rj2/xe9dcOp+KOZYRWuejGRA/00qBujrF4FEEJaIrCUY4nli6u46GMy+1USzjfv3BJ8oM6F2EeV4uDtx7ie1Ddrxy1bqBvJ6I7nKHMYahq/KMqRjq/IqVQzFo19X0eWKWXHEWsZOCaVgcLxv7scTX6PJMKX79LHxjD2hmMNTEVliurw3pMxRHnkFKfW8dClHUmMgqJTUyMHWSRRYuXx4x89HpWa72lYOGzsXVHVnWgS+C4kwtZwSh9sDEjMdU5qSie78pMUN+YcLQZtF8SYGBQDfGRtFWx8jJPiYvaLiBS05vDZhegQykqHwfmao2vfdvA72Cisbxo5sWdcZAiryIlPfmtroJ8wjFwwMl2l+y9egK+0vTm+FCwNFHSY2mbPLXClzay9DqFkg1Q3fl8tpu+syx8b474yy76SGkwxTSVFOOMxj2HiFE32tWk+v8InwOY2l4QX6l6spxBGjLDX8hiSx10z2McLo+XCArWwWPLAHm1/8D", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TRACEABILITY = _t, ESTAÇÃO = _t, #"DESCRIÇÃO FALHA" = _t, Data = _t, #"HR I/F" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type date}, {"HR I/F", type time}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each try test([ESTAÇÃO],[#"HR I/F"]) otherwise "")
in
#"Added Custom"
A sample PBIX. for your reference is attached.
Hope it helps,
Community Support Team _ Caitlyn Yan
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi
Anonymous
I really appreciate your support and I was able to do almost everything with my datasheet, it was very good I really appreciate your help, but if I can check one more problem.
after many attempts, I managed to do it through your advice, to stay automatically when an operator is changed as shown in the table below, look in the table that I changed only on the date 26.08.2021. I don't understand why the operator "charles" appears on other dates, I've tried to change some things in the code but without success.
There was a change of operator (CHARLES)
In the BI power table "FAILURES STATION"
however, I only changed the operator on the date 26/08.2021, and in the table of defects the operator Charles appears on other dates as shown in the table below, I just wanted it to show the operator only on the date it was placed in the table, that is it should not appear on other dates.
Please, when you had some time for me, I would like your help to solve this problem.
thank you very much for the support.
follows the PBIX, with the changes I made based on its code:
https://drive.google.com/file/d/1pHw0YM2GYv-KjEdP0fH1e93h_Z99-RAk/view?usp=sharing
Hi cprpontes ,
Please try the following to see if the problem above still occurs.
Calculate the maximum time and minimum time.
Max Time = CALCULATE ( MAX ( 'operators'[HR I/F] ), FILTER ( 'operators', 'operators'[OPERADOR] = EARLIER ( 'operators'[OPERADOR] ) && 'operators'[ESTAÇÃO] = EARLIER ( 'operators'[ESTAÇÃO] ) ) )Min Time = CALCULATE ( MIN ( 'operators'[HR I/F] ), FILTER ( 'operators', 'operators'[OPERADOR] = EARLIER ( 'operators'[OPERADOR] ) && 'operators'[ESTAÇÃO] = EARLIER ( 'operators'[ESTAÇÃO] ) ) )Then:
REV OPER = CALCULATE ( MAX ( 'operators'[OPERADOR] ), FILTER ( 'operators', 'operators'[Min Time] < EARLIER ( 'Failures'[HR I/F] ) && 'operators'[Max Time] > EARLIER ( 'Failures'[HR I/F] ) && 'operators'[ESTAÇÃO] = EARLIER ( 'Failures'[ESTAÇÃO] ) ) )A sample PBIX. for your reference is attached.
Hope it helps,
Community Support Team _ Caitlyn Yan
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.thank you very much for the support, now it worked as expected, through your help the code was great.
I just added one more condition to get the date linked to the operator. thank you!