Measure looking for value in another table

[Syndicate_Admin]

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!

[Anonymous · Accepted solution]

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.

[Syndicate_Admin]

I 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.

[amitchandak]

@Syndicate_Admin , Create common tables like supervisor and date and use them to create visuals

https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solutio...