DAX match text in multiple columns

[AshleyMartinez]

Hi,

I've been trying to figure out how can I get this report done but I can't find any efficient way to fo it.

I have 2 tables:

[Table1] Sales by brand and date:

Brand	Date	Sales
Apple	Jan-19	2.30
Huawei	Jan-19	2.18
Lenovo	Jan-19	0.80
LG	Jan-19	1.10
Samsung	Jan-19	1.90
Xiaomi	Jan-19	1.70
Apple	Feb-19	2.40
Huawei	Feb-19	2.15
Lenovo	Feb-19	0.60
LG	Feb-19	1.20
Samsung	Feb-19	1.80
Xiaomi	Feb-19	1.60

[Table2] And all the workers responsible (in any way) for each brand. Every worker can have different positions by brand, they can even have more than one position by brand.

Brand	City Agent	Region Agent	Country Agent	Head of Account
Apple	Mike	Carl	Mike	Bryan
Huawei	Jane	Florence	Jane	Bryan
Samsung	Mike	Lucia	Bryan	Bryan
Xiaomi	Carl	Jane	Bryan	Florence
LG	Carl	Florence	Lucia	Lucia
Lenovo	Mike	Mike	Jane	Florence

What I need is to get sales by worker.

The logic is: If I want see Bryan, the formula should look if Bryan is in any of the four columns (city agent, region agent, country agent, head of  account) at least once in [Table2], if so, then bring the sales corresponding to every brand in which Bryan has had a match, like this:

Worker	Brand	Jan-19	Feb-19
Bryan	Apple	2.3	2.4
Bryan	Huawei	2.18	2.15
Bryan	Samsung	1.9	1.8
Bryan	Xiaomi	1.7	1.6
Total		8.08	7.95

*See that Bryan has 2 positions in Samsung (Country Agent and Head of Account) but the sales are not duplicated and that's correct.

Obviously, if I see all the workers in one table, the sales are going to be repeated as many times as different workers asigned to each brand, and that's ok, like this:

Worker	Jan-19	Feb-19	Total
Bryan	8.08	7.95	16.03
Carl	5.1	5.2	10.3
Florence	5.78	5.55	11.33
Jane	4.68	4.35	9.03
Lucia	3	3	6
Mike	5	4.8	9.8
Total	31.64	30.85	62.49

I've created tables to relate [Table1] and [Table2] without success.

Any ideas? PLEASE!