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
AshleyMartinez's avatar
AshleyMartinez
Frequent Visitor
6 years ago

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!

 

 

  • smpa01's avatar
    smpa01
    Community Champion
    6 years ago

    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