Forum Discussion

AshleyMartinez's avatar
AshleyMartinez
Frequent Visitor
6 years ago

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: Brand Date Sales Apple...
Need Help
smpa01's avatar
smpa01
Community Champion
6 years ago

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.