DAX match text in multiple columns

AshleyMartinez ,

Created a column in PBI, although you would be better off in PQ, an If statement shown below. You still have some work to do,

Filter out the empty rows using the visual filter.

Let me know if you have any questions,

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Bryan = IF(Brand[City Agent] = "Bryan" || Brand[Country Agent] ="Bryan" || Brand[Head of Account] = "Bryan" || Brand[Region Agent] = "Bryan",Brand[Brand])

f

AshleyMartinez ,

If you drop the new sales person column into the table such as "Mike" and filter out the blanks, and drop the "Mike" as a slicer you can then click on the company, and see "Mike" sales for the company.

Hope this helps your report,

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Hi AshleyMartinez ,

So, you say that you have created tables to link these two tables, but you are not successful.

How about Brand to Brand like this? 

Can you give us a picture of your model?

Nathaniel_C 

AshleyMartinez  my approach is following

A. Create a table as following called NewTbl

NewTbl = 
VAR _x = SELECTCOLUMNS (
    'Table 2',
    "Name", 'Table 2'[City Agent],
    "Brand", 'Table 2'[Brand]
)
VAR _y = SELECTCOLUMNS (
    'Table 2',
    "Name", 'Table 2'[Country Agent],
    "Brand", 'Table 2'[Brand]
)
VAR _z = SELECTCOLUMNS (
    'Table 2',
    "Name", 'Table 2'[Head of Account],
    "Brand", 'Table 2'[Brand]
)
VAR _a= SELECTCOLUMNS (
    'Table 2',
    "Name", 'Table 2'[Region Agent],
    "Brand", 'Table 2'[Brand]
)
 VAR _t= DISTINCT
                 (
                   UNION(_x,_y,_z,_a)
                 )
          
VAR _d= DISTINCT('Table 1'[Date])
VAR _cross = CROSSJOIN(_t,_d)
RETURN _cross

B.  Create a calculated column as following

Sales = LOOKUPVALUE('Table 1'[Sales],'Table 1'[Brand],NewTbl[Brand],'Table 1'[Date],NewTbl[Date])

 which gives the following

I am very interested to see now to see if this can be solved by creating a measure though.

Hi AshleyMartinez , smpa01 ,

Automated change in PQ.

let
    Source = Excel.Workbook(File.Contents("C:\Users\nat_c\OneDrive\Desktop\SalesBrand.xlsx"), null, true),
    Brand_Table = Source{[Item="Brand",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Brand_Table,{{"Brand", type text}, {"City Agent", type text}, {"Region Agent", type text}, {"Country Agent", type text}, {"Head of Account", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Brand"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Brand", "Value"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Removed Duplicates" = Table.Distinct(#"Merged Columns"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Duplicates", "Merged", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", type text}, {"Merged.2", type text}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Merged.1", "Brand"}, {"Merged.2", "Emp"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns1",{"Emp", "Brand"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Emp", Order.Ascending}, {"Brand", Order.Descending}})
in
    #"Sorted Rows"

Went from 

to this,

So this at least is automated vs the if statement.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Hi! smpa01 

I'm working on your approach, and it's working flawless with the example. Now, applying this to my actual data, I have a problem and is that I have more than one row of sales per day for the same brand, which means that the measure added ("Sales") in NewTbl has to sum sales grouped by "Name", "Brand", "Date". I've been trying Summarize but the error is: "the expression refers to multiple columns.."

Nathaniel_C 

I unpivot Table 2 but I don't know why I can't get the same results as you, can you share your file?

Thank you so much for your time!

The new sales table would be like this: