Forum Discussion
How to Dynamically Set Field Parameter Title Based on a Slicer in DAX?
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!
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.
5 Replies
- vojtechsima
Super User
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.
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 TeamIf 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
Memorable Member
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?
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.
