Dynamic formula based on corresponding column

[Anonymous]

Hi everyone,

I have the following formula:

United Measure =

VAR SelectedColumn = SELECTEDVALUE('C'[C])

Return SWITCH(SelectedColumn;

"C1"; SUMX('E&A';'E&A'[Hours]*RELATED('A&C'[C1]));

"C2"; SUMX('E&A';'E&A'[Hours]*RELATED('A&C'[C2]));

"C3"; SUMX('E&A';'E&A'[Hours]*RELATED('A&C'[C3]))

• My goal is to fill the matrix at the bottom without repeating the line ("C1"; SUMX('E&A';'E&A'[Hours]*RELATED('A&C'[C1]));) for each single C's (about 100 columns).

As a quick run down, those are the table I am using:

Table 1: (List of A's)

A's

A1

A2

A3

...

A100

Table 2. (List of E's)

E's

E1

E2

E3

...

E100

Table 3. (List of C's)

C's

C1

C2

C3

...

C100

Table 4. (Relation between A's and each C's)

A's	C1	C2	C3	...	C100
A1	1	1	1	...	0
A2	0	1	1	...	1
A3	1	0	1	...	1
...	...	...	...	...	...
A100	1	1	0	...	1

Table 5. (Relation between A's and E's in hours)

E's	A's	Hours
E1	A1	3
E1	A2	2
E1	A3	5
E2	A1	1
E2	A100	7
E3	A1	3
E3	A3	4
E3	A100	2
...
E100	A2	1

End matrix:

E's	C1	C2	C3	...	C100
E1	8
E2
E3
...
E100

I hope this is understandable.

Best regards,

[parry2k]

@Anonymous can you share sample pbix file with expected output and can look at it. It is bit hard to understand and reproduce the dataset from your post.