How to Dynamically Set Field Parameter Title Based on a Slicer in DAX?

[Jskdlai23]

Hi everyone,

I'm working on a Power BI report and trying to set the title of a field parameter dynamically using a SELECTEDVALUE. The goal is to make the column title in a matrix update based on a slicer selection.

Here’s what I’ve tried so far:

DynamicRevenue =
{
    ( "Revenue "
        & FORMAT (
            ( SELECTEDVALUE ( SalesInvoiceLinesIncremental[Invoice Date].[Year] ) - 1 ),
            "0"
        ), NAMEOF ( 'SalesInvoiceLinesIncremental'[Previous Year Sales Lines] ), 0 ),
    ( "Revenue "
        & FORMAT (
            ( SELECTEDVALUE ( SalesInvoiceLinesIncremental[Invoice Date].[Year] ) ),
            "0"
        ), NAMEOF ( 'SalesInvoiceLinesIncremental'[Current Year Sales Lines] ), 1 )
}

However, this doesn't seem to work as expected. It throws an error, and I suspect it might be due to the way I'm trying to define the field parameter or how I'm using SELECTEDVALUE.

I'm specifically looking to:

• Dynamically update the column title based on the slicer selection.
• Have separate calculations for "Previous Year Sales Lines" and "Current Year Sales Lines" linked to the selected year.

This is being used in a matrix visual. Does anyone know how to achieve this or have any alternative solutions?

Thanks in advance for your help!

[eva_t1984]

@vojtechsima 

Can you share an example of how you continuen with the selectedvaule for the new created column? 

I have 2 slicers in my report "Month" and "Year" and I want the measure to display YTD January 2025, YTD January 2024, YTD January 2023.... I am not quite sure how to continue, once I added the new calculated column within the field parameter. 

Thank you!

[Element115]

Perhaps this might help:

So, the Display slicer shows just a name. Depending on what the slicer selection is, this is returned by the SELECTEDVALUE() function inside a measure defined on the FieldSelector table.  The SWITCH() function simply decides which measure is called depending on the incoming name chosen in the slicer, like so: 

show_selected_field = 
    SWITCH (
        SELECTEDVALUE ( 'FieldSelector'[MeasureName] ),
        "Velocity", 'TS_MEASURES'[TS_AVG_VELOCITYxHOUR],
        "Volume", 'TS_MEASURES'[TS_AVG_VOLxHOUR]
    )

'TS_MEASURES' is just a dummy table that contains all my measures.  Measures have global scope within a report. In other words, if you define a measure on table T0, this measure is visible from any other table.  So, if one have a huge amount of tables and does not remember which measure is associated with with table, it is easier to find all measures if they are all centralized under one dummy table.  

[Anonymous]

Hi @Jskdlai23 ,

Thanks for the reply from vojtechsima .

Your requirement is that you want to dynamically update the matrix column headers based on the year selected by the user, right? For example, if you select 2025, the column headings should be displayed as “Revenue 2024” and “Revenue 2025”, and then the sales data for the selected year and the previous year will be calculated and displayed in the matrix. Is there any error in my understanding?

This is the data I am using for testing:

Invoice Date	Name	Sales
2023-01-15	Charlie	850
2023-01-20	David	550
2023-02-10	Eva	950
2023-02-25	Alice	450
2023-03-10	Bob	650
2023-03-20	Charlie	850
2023-04-01	David	300
2024-01-15	Charlie	800
2024-01-20	David	600
2024-02-10	Eva	900
2024-02-25	Alice	500
2024-03-10	Bob	700
2024-03-20	Charlie	800
2024-04-01	David	200
2025-01-01	Alice	500
2025-01-02	Bob	700
2025-01-03	Charlie	800
2025-01-04	David	600
2025-01-05	Eva	900
2025-01-06	Alice	500
2025-01-07	Bob	700

Instead of using a field parameter, I use the following code to create a new table with the year and previous year's data and add a new column called DisplayName with a value of “Revenue ” plus the year. The filter conditions ensure that only the current year and the previous year's data are included.

Table = 
ADDCOLUMNS (
    SUMMARIZE (
        FILTER (
            CROSSJOIN (
                SUMMARIZE (
                    ADDCOLUMNS (
                        'SalesInvoiceLinesIncremental',
                        "Year", YEAR ( 'SalesInvoiceLinesIncremental'[Invoice Date] )
                    ),
                    [Year]
                ),
                SUMMARIZE (
                    UNION (
                        SUMMARIZE (
                            ADDCOLUMNS (
                                'SalesInvoiceLinesIncremental',
                                "_Year", YEAR ( 'SalesInvoiceLinesIncremental'[Invoice Date] ) - 1
                            ),
                            [_Year]
                        ),
                        SUMMARIZE (
                            ADDCOLUMNS (
                                'SalesInvoiceLinesIncremental',
                                "Year", YEAR ( 'SalesInvoiceLinesIncremental'[Invoice Date] )
                            ),
                            [Year]
                        )
                    ),
                    [_Year]
                )
            ),
            [_Year] >= [Year] - 1
                && [_Year] <= [Year]
        ),
        [Year],
        [_Year]
    ),
    "DisplayName", "Revenue " & [_Year]
)

Creates a measure that calculates the sum of all sales in the SalesInvoiceLinesIncremental table with a sales date year equal to the largest year in the current context (i.e., _currentYear).

Measure = 
VAR _currentYear =
    MAX ( 'Table'[_Year] )
RETURN
    CALCULATE (
        SUM ( SalesInvoiceLinesIncremental[Sales] ),
        YEAR ( 'SalesInvoiceLinesIncremental'[Invoice Date] ) = _currentYear
    )

Use the “Table” table Year to filter, use the matrix visual object to display the data, the final visual effect is shown below:

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Element115]

Yes, cool but too complex for such a simple ask, I'd say.  Also, SUMMARIZE has been deprecated for years now. 

For instance, I have a requirement where the user wants to choose any number of years in a 50 year period, and not contiguous years necessarily. For ex, 2015, 2019, and 2023.    

Unfortunately, when the top level column name is, let's say a DimDate->Year, and you got a sublevel, ie another set of column titles coming from measures referenced in your param table, then these titles are NOT displayed if all params are NOT selected in the slicer. 

When the param names/titles are at the top level, they display properly even if not all params are selected.

My contention is that there should be a better way than writing complex DAX code to fix this.

Is this by design or a bug? 

[vojtechsima · Accepted solution]

Hello, @Jskdlai23 
you can't use SELECTEDVALUE for Field Parameter in its pure form.

There's a quick workaround tho:
Field parameter as it is:

Create new calculated column inside the field parameter table like this, basically referencing the first column

Then use SELECTEDVALUE for newly created column that mirrors the original one.