Dynamic names for the fields in the "Values" area of a matrix

[webportal]

Is there any way of defining dynamic names for the fields in the "Values" area of a matrix?

I know we can double click on them and manually change the name, but can we make it dependent on a slicer value, for example?

Thanks in advance!

[v-eqin-msft · Accepted solution]

Hi @webportal ,

Sorry for late reply ,you could use the following formula:

flagForSelected =
VAR _sele =
    SELECTEDVALUE ( YearSlicer[year] )
RETURN
    IF (
        _sele = SELECTEDVALUE ( Dynamic2[Year] )
            || _sele
                = SELECTEDVALUE ( Dynamic2[Year] ) + 1,
        1
    )

toRemoveOtherColumns =
SUM ( Dynamic2[Count] ) * [flagForSelected]

sumForRow =
SUMX ( Dynamic2, [toRemoveOtherColumns] )

The matrix looks like this:

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[v-eqin-msft]

Hi @webportal ,

According to your description, you want to dynamically display values in matrix based on slicer, right?

My data sample looks like this:

After doing “Pivot” for “Date” column(value column is “Count”) in Query Editor ,you could use the following formula:

lastYear =
VAR a =
    SUM ( 'Dynamic'[2017] )
VAR b =
    SUM ( 'Dynamic'[2018] )
VAR c =
    SUM ( 'Dynamic'[2019] )
VAR selector =
    MAX ( ForSlicer[Year] )
RETURN
SWITCH ( selector, 2017, BLANK (), 2018, a, 2019, b, 2020, c )

thisYear =
VAR a =
    SUM ( 'Dynamic'[2017] )
VAR b =
    SUM ( 'Dynamic'[2018] )
VAR c =
    SUM ( 'Dynamic'[2019] )
VAR d =
    SUM ( 'Dynamic'[2020] )
VAR selector =
    MAX ( ForSlicer[Year] )
RETURN
SWITCH ( selector, 2017, a, 2018, b, 2019, c, 2020, d )

My matrix visual looks like this:

Is the result what you want? If not, please upload some data samples and expected output.

Please do mask sensitive data before uploading.

Best Regards,

Eyelyn Qin

[webportal]

Hello @v-eqin-msft 

Thanks for helping.

To achieve that solution, it would be enough to simply double click the measures names and replace them by "this year" and "last year".

What I need is this:

[edhans]

Hi @webportal I am not aware of a way to change the column names based on a slicer selection. You can change the names in Power Query, before the import, but once in the Power BI DAX side, that name is fixed.

You can change the measure used based on a slicer selection, and use conditional formatting to change what is shown in the report. See this article on how to change the measure used. It uses SWITCH() to work.

There are some components of a visual that can be based on a measure, like the visual title. You can use conditional logic to change what the visual title is for example:

But I know of no way to change the actual field name.

[webportal]

Hi @edhans and@amitchandak 

Thanks for helping, maybe I haven't explained myself properly.

I want to change the names of two measures in the "Values" field:

[SalesThisYear]

[SalesLastYear]

If the user selected 2020 in the slicer, this would mean the names of what I see in the matrix would be:

2020

2019

[edhans]

@webportal Just put the year field from your date table in your visual as an axis, column header, etc. The slicer will filter it to show the proper year as that is the same field.

Your data is sales (or whatever.) Sales is sales. Sales isn't 2019 or 2020. The years are dimensions that describe sales, and those will change based on a slicer selection. But sales is just sales. It will recalculate to only show the total for 2019, or 2020, or both if you have multi-select enabled for the slicer.

Make sense?

[webportal]

@edhans, thanks for your help.
However, I have more than these two columns on the matrix. For example, I have a 3rd column with the variation from one year to another, so placing the year on the columns wouldn't work.

[amitchandak]

@webportal , refer if this can help

dynamic Column Header
https://www.youtube.com/watch?v=yEemVBiaTuk
https://www.goodly.co.in/dynamic-column-names-power-query/
https://www.thebiccountant.com/2018/07/04/efficiently-rename-column-names-with-a-function-in-power-b...
https://community.powerbi.com/t5/Desktop/Dynamic-column-name-from-its-value/td-p/189442