Aliasing Columns inside SUMMARIZE

[SimonNU]

A simple request: I'm trying to name the columns in the SUMMARIZE function but I can't figure out how. For example, I'd simply like to rename "Payroll[Employee Name]" to something a little more user friendly. Any suggestions? It's rather easy to do with ADDCOLUMNS...

EVALUATE
ADDCOLUMNS (
    SUMMARIZE (
        FILTER ( Payroll, Payroll[Employee Code] = "12345" ),
        Payroll[Employee Name],
        Payroll[Employee Code],
        'Level Table'[Employee Level Name],
        Payroll[Employee DOB],
        'Date Table'[Transaction Date],
        'Date Table'[Period]
    ),
    "Hours", [Measure Hours],
    "Hourly Wage", [Measure Hourly Wage],
    "Total Income", [Measure Total Income]
)
ORDER BY Payroll[Transaction Date]

Thanks,
Simon

 

[Salle]

Hello,
Yes, ADDCOLUMNS is the way to go:

EVALUATE
ADDCOLUMNS (
    SUMMARIZE (
        FILTER ( Payroll, Payroll[Employee Code] = "12345" ),
        Payroll[Employee Name],
        Payroll[Employee Code],
        'Level Table'[Employee Level Name],
        Payroll[Employee DOB],
        'Date Table'[Transaction Date],
        'Date Table'[Period]
    ),
    [COLOR=#ff0000]"Name", Payroll[Employee Name],[/COLOR]
    "Hours", [Measure Hours],
    "Hourly Wage", [Measure Hourly Wage],
    "Total Income", [Measure Total Income]
)
ORDER BY Payroll[Transaction Date]

Or if you want a table with just the Aliases:

[FONT=arial]EVALUATE
SUMMARIZE(
    ADDCOLUMNS(
        FILTER ( Payroll, Payroll[Employee Code] = "12345" ),
[SIZE=2][FONT=Verdana]          "Name", Payroll[Employee Name],[/FONT][/SIZE]
[SIZE=2]             "Hours", [Measure Hours],[/SIZE]
[SIZE=2]             "Hourly Wage", [Measure Hourly Wage],[/SIZE]
[SIZE=2]             "Total Income", [Measure Total Income[/SIZE]
    ),
    [Name],
    [Hours],
    [Hourly Wage],
    [Total Income]
)
[/FONT]

/Daniel

 

[SimonNU]

Thanks Daniel!

Your first method will duplicate the columns: once for the SUMMARIZE and then again for ADDCOLUMNS.

I think the second one will work but I avoided it due to slow query execution. I'll do some testing on it today.


Simon

 

[SimonNU]

Unfortunately method 2 is very slow.

Original approach: 122 milliseconds
Method 2: 12 seconds

 

[Salle]

Oh really? Strange...wonder why that is.
Perhaps it has got something to do with having ADDCOLUMNS in the first step after FILTER, that it produces such a big table.
Maybe something like this instead?

EVALUATE
SUMMARIZE(
    ADDCOLUMNS (
         SUMMARIZE (
             FILTER ( Payroll, Payroll[Employee Code] = "12345" ),
             Payroll[Employee Name],
             Payroll[Employee Code],
             'Level Table'[Employee Level Name],
             Payroll[Employee DOB],
             'Date Table'[Transaction Date],
             'Date Table'[Period]
         ),
         "Name", Payroll[Employee Name],
         "Hours", [Measure Hours],
         "Hourly Wage", [Measure Hourly Wage],
         "Total Income", [Measure Total Income]
    ),
[Name],
[Hours],
[Hourly Wage],
[Total Income]
)