Forum Discussion

swolfe2's avatar
swolfe2
Icon for Helper I rankHelper I
3 years ago
Solved

Use Variable As Column Header in SUMMARIZECOLUMNS

I am attempting to dynamically name a column using SUMMARIZECOLUMNS and variables.

This code works, and I can tell that the variables are returning correctly. However, when I try to replace the text between the quotes in SUMMARIZECOLUMNS with the string variables, I get an error:

 

Calculate Table = 
VAR _today =
    TODAY ()
VAR _2Year =
    YEAR ( _today ) - 2
VAR _2YearString =
    CONVERT ( _2Year, STRING )
VAR _1Year =
    YEAR ( _today ) - 1
VAR _1YearString =
    CONVERT ( _1Year, STRING )
RETURN
    SUMMARIZECOLUMNS (
        'Final Table'[Category],
        "FY2020", CALCULATE ( SUM ( 'Final Table'[Value] ), 'Date Table'[Year] = _2Year ),
        "FY2021", CALCULATE ( SUM ( 'Final Table'[Value] ), 'Date Table'[Year] = _1Year )
    )

 

 

I would really like to dynamically name these columns so that when the next fiscal year rolls around, I don't have to update the DAX string in the header from "FY2020" to "FY2021".

 

Here's a link to the .pbix in Google Drive.

 

Any help/advice would be greatly appreciated!

Need Help
  • v-xiaosun-msft's avatar
    v-xiaosun-msft
    3 years ago

    Hi swolfe2 ,

     

    According to your description, here is my solution.

    Currently it is not supported to change the column name dynamically with the variables.

    I suggest that you can create two tables and then create two columns to return the variables separately. Here are steps.

    Sample data:

    Create the first table.

     

    Table =
VAR _today =
    TODAY ()
VAR _2Year =
    YEAR ( _today ) - 2
VAR _2YearString =
    CONVERT ( _2Year, STRING )
RETURN
    SUMMARIZE (
        'Final Table',
        "_2Year",
            CALCULATE (
                SUM ( 'Final Table'[VALUE] ),
                YEAR ( 'Final Table'[Column1] ) = _2Year
            )
    )

     

    Create a column in the "table".

     

    Column = CONVERT(YEAR(TODAY())-2,STRING)

     

    Then click the "fx" under the title settings, and choose "column", you will get the output as below.

    Use the same steps to create another "Table 2".

     

    Table 2 =
VAR _today =
    TODAY ()
VAR _1Year =
    YEAR ( _today ) - 1
VAR _1YearString =
    CONVERT ( _1Year, STRING )
RETURN
    SUMMARIZE (
        'Final Table',
        "_1Year",
            CALCULATE (
                SUM ( 'Final Table'[VALUE] ),
                YEAR ( 'Final Table'[Column1] ) = _1Year
            )
    )
    Column2 =
CONVERT ( YEAR ( TODAY () ) - 1, STRING )

     

    Final output:

    Or maybe you can try to reference the document as below which do it in Power Query.

    Using a Variable as a Column Name in Power Query - Microsoft Q&A

     

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ xiaosun

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-xiaosun-msft's avatar
    v-xiaosun-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi swolfe2 ,

     

    According to your description, here is my solution.

    Currently it is not supported to change the column name dynamically with the variables.

    I suggest that you can create two tables and then create two columns to return the variables separately. Here are steps.

    Sample data:

    Create the first table.

     

    Table =
VAR _today =
    TODAY ()
VAR _2Year =
    YEAR ( _today ) - 2
VAR _2YearString =
    CONVERT ( _2Year, STRING )
RETURN
    SUMMARIZE (
        'Final Table',
        "_2Year",
            CALCULATE (
                SUM ( 'Final Table'[VALUE] ),
                YEAR ( 'Final Table'[Column1] ) = _2Year
            )
    )

     

    Create a column in the "table".

     

    Column = CONVERT(YEAR(TODAY())-2,STRING)

     

    Then click the "fx" under the title settings, and choose "column", you will get the output as below.

    Use the same steps to create another "Table 2".

     

    Table 2 =
VAR _today =
    TODAY ()
VAR _1Year =
    YEAR ( _today ) - 1
VAR _1YearString =
    CONVERT ( _1Year, STRING )
RETURN
    SUMMARIZE (
        'Final Table',
        "_1Year",
            CALCULATE (
                SUM ( 'Final Table'[VALUE] ),
                YEAR ( 'Final Table'[Column1] ) = _1Year
            )
    )
    Column2 =
CONVERT ( YEAR ( TODAY () ) - 1, STRING )

     

    Final output:

    Or maybe you can try to reference the document as below which do it in Power Query.

    Using a Variable as a Column Name in Power Query - Microsoft Q&A

     

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ xiaosun

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.