1

I have one Date column in my fact table, and due to some client api requirement I need to format this column as string while grouping data using SUMMARIZE function. Below is the sample query, which I am using:

EVALUATE(
CALCULATETABLE(
    ADDCOLUMNS(
        SUMMARIZE(
            'BreakTable',
            'BreakTable'[Column1],
            'BreakTable'[Column2],
            'BreakTable'[DateColumn1], --This needs to be formatted 
        ),
        "BreakCount",FORMAT('BreakTable'[BreakCount],"#,##0")
    )
))

I have tried using FORMAT function in SUMMARIZE, and that does not work by default. I can not add new column to FactTable, so need to solve this while querying itself.

Is there any other way to achieve this? Any help is appreciated.

As per suggestion, adding more information.

We are using Sql Server 2014.

AjayK
  • 433
  • 5
  • 18

1 Answers1

1

You could use the SELECTCOLUMNS() function. This function works similarly to the ADDCOLUMNS() function, except that it only returns the columns you specify.

Here is an example of how you can alter your existing query:

EVALUATE(
    SELECTCOLUMNS(
        SUMMARIZE(
            'BreakTable',
            'BreakTable'[Column1],
            'BreakTable'[Column2],
            'BreakTable'[DateColumn1], 
        ),
        "Column1", [Column1],
        "Column2", [Column2],
        "DateColumn1", FORMAT([DateColumn1],"YourFormatHere"), --Format your DateColumn here
        "BreakCount",FORMAT('BreakTable'[BreakCount],"#,##0")
    )
)

EDIT:

Please note that the SELECTCOLUMNS() function is only available from SQL Server 2016 and up.

dybzon
  • 1,236
  • 2
  • 15
  • 21