Forum Discussion
AdministratorMeasure looking for value in another table
Hello, I expose my case to see if you can help me again.
I have 2 related tables (DAILY Table and HISTORICAL Table) by the "Supervisor" column and also by the "Operator" column (although this relationship is inactive)
I am trying to show a report in which you see for the column "Supervisor" in the DAILY table, the data of a third column ("Incidents" that is in "HISTORY") and also the data of "operator" in that same row.
I mean:
DAILY Table:
| Date | Supervisor | Operative |
| 07/11/22 | A | 1 |
| 07/11/22 | B | 2 |
| 07/11/22 | C | 3 |
| 07/11/22 | D | 4 |
| 07/11/22 | E | 5 |
| 07/11/22 | F | 6 |
HISTORICAL TABLE:
| Date | Supervisor | Operative | Incidents |
| 01/11/22 | A | 1 | 7 |
| 03/10/21 | A | 1 | 15 |
| 06/06/20 | A | 2 | 3 |
| 05/04/19 | A | 2 | 1 |
| 01/10/22 | B | 1 | 1 |
| 06/09/22 | B | 3 | 6 |
| 05/08/22 | B | 3 | 3 |
| 04/07/21 | C | 2 | 2 |
| 13/06/21 | C | 5 | 1 |
| 10/06/20 | C | 3 | 2 |
| 05/08/22 | D | 6 | 5 |
| 25/07/22 | D | 5 | 12 |
Desired result:
| Supervisor | Operative | Incidents Supervisor | Incidents Operator |
| A | 1 | 26 (sum of incidents in HISTORICAL table for supervisor A) | 23 (sum of incidents in HISTORICAL table for Operator 1) |
| B | 2 | 10 (sum of incidents in HISTORICAL table for supervisor B) | 6 (sum of incidents in HISTORICAL table for Operator 2) |
| C | 3 | 5 | 5 |
| D | 4 | 5 | 0 |
The sum of the supervisor there is no problem but as soon as I think about how to add the operator I do not get any report similar to what I have described.
Thanks for reading!
Hi Syndicate_Admin ,
I have created a simple sample, please refer to it to see if it helps you.
Create meaures.
Incidents = CALCULATE(SUM(history[Incidents]),FILTER(ALL(history),history[Supervisor]=SELECTEDVALUE(daily[Supervisor])))Supervisor1 = var _1= CALCULATE(SUM(history[Incidents]),FILTER(ALL(history),history[Supervisor]=SELECTEDVALUE(daily[Supervisor])&&history[Operative]=SELECTEDVALUE(daily[Operative]))) return IF(_1=BLANK(),0,_1)If I have misunderstood your meaning, please provide mroe details with your desired output.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandak
Super User
Syndicate_Admin , Create common tables like supervisor and date and use them to create visuals
Syndicate_AdminI didn't quite understand his solution.
The "HISTORICO" table has more than 300,000 records. According to your solution, would you have to duplicate some columns of this table (supervisory date) to make new relationships? I think that would make the report much heavier than it already is now.
I was trying to do something with the CALCULATE, FILTER and ALL functions but I did not get the expected result.
Hi Syndicate_Admin ,
I have created a simple sample, please refer to it to see if it helps you.
Create meaures.
Incidents = CALCULATE(SUM(history[Incidents]),FILTER(ALL(history),history[Supervisor]=SELECTEDVALUE(daily[Supervisor])))Supervisor1 = var _1= CALCULATE(SUM(history[Incidents]),FILTER(ALL(history),history[Supervisor]=SELECTEDVALUE(daily[Supervisor])&&history[Operative]=SELECTEDVALUE(daily[Operative]))) return IF(_1=BLANK(),0,_1)If I have misunderstood your meaning, please provide mroe details with your desired output.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
