cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

DAX match text in multiple columns

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:

BrandDateSales
AppleJan-192.30
HuaweiJan-192.18
LenovoJan-190.80
LGJan-191.10
SamsungJan-191.90
XiaomiJan-191.70
AppleFeb-192.40
HuaweiFeb-192.15
LenovoFeb-190.60
LGFeb-191.20
SamsungFeb-191.80
XiaomiFeb-191.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.

BrandCity AgentRegion AgentCountry AgentHead of Account
AppleMikeCarlMikeBryan
HuaweiJaneFlorenceJaneBryan
SamsungMikeLuciaBryanBryan
XiaomiCarlJaneBryanFlorence
LGCarlFlorenceLuciaLucia
LenovoMikeMikeJaneFlorence

 

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:

 

WorkerBrandJan-19Feb-19
BryanApple2.32.4
BryanHuawei2.182.15
BryanSamsung1.91.8
BryanXiaomi1.71.6
Total 8.087.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:

 

WorkerJan-19Feb-19Total
Bryan8.087.9516.03
Carl5.15.210.3
Florence5.785.5511.33
Jane4.684.359.03
Lucia336
Mike54.89.8
Total31.6430.8562.49

 

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

Any ideas? PLEASE!

11 REPLIES 11

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors