Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AshleyMartinez
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
AshleyMartinez
Frequent Visitor

The new sales table would be like this:

 

BrandDateSales
AppleJan-192.3
AppleJan-190.4
HuaweiJan-192.18
LenovoJan-190.8
LGJan-191.1
SamsungJan-191.9
SamsungJan-191.3
XiaomiJan-191.7
AppleFeb-192.4
HuaweiFeb-192.15
LenovoFeb-190.6
LenovoFeb-190.2
LGFeb-191.2
SamsungFeb-191.8
XiaomiFeb-191.6

Hi @AshleyMartinez , @smpa01 ,

I believe this is the output for which you were looking... created the table that I show in the picture. Automated in PQ. Then did a join between the two tables.  It is many to many, so you might want to consider an intermediate table, but it works.  Here is the pbix. My pbix 

 

I had to recreate this as I went down another road trying to set your data in a normal fashion. Exploring a table for each classification of agent would be for starters.  I find if one uses a star schema from the beginning things work better down the road. You may have this, and we are only seeing a couple of tables.

 

How ever, I was able to rebuild it from what I had posted here.  Anyway this seems to meet your requirements. Please 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

 

 

Brand Sales_1.PNG

 

Brand Sales_3.PNG

Brand Sales_2.PNG

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




AshleyMartinez
Frequent Visitor

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!

 

 

@AshleyMartinez  can you please try this.

 

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(SELECTCOLUMNS('Table 5',"Date",'Table 5'[Date]))
VAR _cross = CROSSJOIN(_t,_d)
VAR _new = SELECTCOLUMNS(
    'Table 5',
    "Brand",'Table 5'[Brand]&"",
    "Date",'Table 5'[Date],
    "Sales",'Table 5'[Sales]
)
VAR _sz = NATURALLEFTOUTERJOIN(_cross,_new)
RETURN _sz

Capture.PNG

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Nathaniel_C
Super User
Super User

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 

 

Brand Sales3.PNG

to this,

Brand Sales4.PNG

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

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




 

 

@AshleyMartinez  @smpa01 Brand Sales5.PNG

 

So we can get the results using filters. Just need some measures.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




smpa01
Super User
Super User

@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

Capture.PNG

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

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Nathaniel_C
Super User
Super User

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 

 

Brand Sales2.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Nathaniel_C
Super User
Super User

Brand Sales1.PNG

@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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Nathaniel_C
Super User
Super User

@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

Brand Sales.PNG

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @Nathaniel_C , thanks for the early reply!

The thing is that my actual data base is very large, I have more than 70 sales persons and I'd like to automate the query. The problem with hardcode like typing "Bryan" in the DAX formula is that i'll have to create it 70 times for each worker.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.