Automate operator for each change in root table

[cprpontes]

I have two separate tables, where 1 has the failures and the times that happened each one of them, the other table is the names of the operators that test a product where failures happen for each operator.
I need to automate where if I change the operator in the operator source table. the most I could be to do like this, but if I change the operator, I have to change the code manually.

I appreciate all the support needed and I don't know if the understanding was explained.

tables of operators:

table of failures:

my code:

REV OPER = SWitch(True(),

'FAILURES STATION'[HR I/F] > TIME(06,55,0) && 'FAILURES STATION'[HR I/F] < TIME(09,10,0) && 'FAILURES STATION'[ESTAÇÃO]="FAT", "MOTA",

'FAILURES STATION'[HR I/F] > TIME(14,35,0) && 'FAILURES STATION'[HR I/F] < TIME(16,57,0) && 'FAILURES STATION'[ESTAÇÃO]="CS", "MOTA",

'FAILURES STATION'[HR I/F] > TIME(09,10,0) && 'FAILURES STATION'[HR I/F] < TIME(11,20,0) && 'FAILURES STATION'[ESTAÇÃO]="FAT", "SERGIO",

'FAILURES STATION'[HR I/F] > TIME(06,55,0) && 'FAILURES STATION'[HR I/F] < TIME(09,10,0) && 'FAILURES STATION'[ESTAÇÃO]="CS", "SOCORRO",

'FAILURES STATION'[HR I/F] > TIME(14,35,0) && 'FAILURES STATION'[HR I/F] < TIME(16,59,0) && 'FAILURES STATION'[ESTAÇÃO]="FAT", "SOCORRO",

'FAILURES STATION'[HR I/F] > TIME(12,15,0) && 'FAILURES STATION'[HR I/F] < TIME(14,30,0) && 'FAILURES STATION'[ESTAÇÃO]="FAT", "WILLAMES",

'FAILURES STATION'[HR I/F] > TIME(09,15,0) && 'FAILURES STATION'[HR I/F] < TIME(11,20,0) && 'FAILURES STATION'[ESTAÇÃO]="CS", "WILLAMES",

'FAILURES STATION'[HR I/F] > TIME(12,15,0) && 'FAILURES STATION'[HR I/F] < TIME(14,30,0) && 'FAILURES STATION'[ESTAÇÃO]="CS", "JUCELINO")

I would like it to be like this, however automatic when in the source table is changed, operator.

https://drive.google.com/file/d/1YbzgAeCnC-zn3MRCfgIqJA3awktqiDcL/view?usp=sharing

[v-xiaoyan-msft]

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.

[cprpontes]

Hi 

@v-xiaoyan-msft 

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

[v-xiaoyan-msft · Accepted solution]

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.

[cprpontes]

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!