Dynamic Column Name change Based on Slicer Selections

[vs_7]

hi i have calculated  dax , where i want change column name in dax.

example: in slicer i select 2021-2022, column name should be change  current Year to  "2021-2022"

and if i select 2020-2021 and 2021-2022 , column name should be change  current Year to  "2021-2022" and Pre Year to 2020-2021

current Year =

CALCULATE(sum(Sales[F Net Value_CR]),FILTER(Master_Calendar,Master_Calendar[F Year]=year(TODAY()-365) ))

Pre Year = CALCULATE(sum(Sales[F Net Value_CR]),FILTER(Master_Calendar,Master_Calendar[F Year]=year(TODAY()-730)))

below is output

slicer is below.

@mwegener  

if i select a particular year then matrix columns should change and show values for that year.

please help to set values dynamic on slicer selection on matrix columns.

example: 

if i select 2018 then output will be change and as per below columns.

BH |2018-2019 | 2019-2020| Change | change%

please suggest.

[vs_7]

Hi @v-shex-msft ,

As you suggested i have unconnected table and tried above measure.

But i'm getting below error in measure. please suggest

measure:

formula =

VAR currFY =

SELECTEDVALUE ( Master_Calendar[F Year] )

VAR selectedFY =

SELECTEDVALUE ( Header[Display Value])

VAR selectedPFY =

( VALUE ( LEFT ( selected, 4 ) ) - 1 ) & "-"

& LEFT ( selected, 4 )

RETURN

IF (

currFY IN { selectedFY, selectedPFY },

VAR range =

CALCULATETABLE (

VALUE (Master_Calendar[Date]),

FILTER ( ALLSELECTED ( Master_Calendar ), Master_Calendar[F Year] = currFY )

)

RETURN

CALCULATE (

SUM ( Sales[F Net Value_CR] ),

FILTER ( ALLSELECTED ( Sales ), [CDate] IN range ),

VALUES ( Sales[MAT_GRP_NAME] )

)

)

[v-shex-msft]

HI @vs_7,

It seems like I canceled and restored some changes on my expression before I put them into the DAX formatter. Please check the following modified formulas if help:

formula =
VAR currFY =
    SELECTEDVALUE ( Master_Calendar[F Year] )
VAR selectedFY =
    SELECTEDVALUE ( NewTable[F Year] )
VAR selectedPFY =
    ( VALUE ( LEFT ( selectedFY, 4 ) ) - 1 ) & "-"
        & LEFT ( selectedFY, 4 )
RETURN
    IF (
        currFY IN { selectedFY, selectedPFY },
        VAR range =
            CALCULATETABLE (
                VALUES ( Master_Calendar[Date] ),
                FILTER ( ALLSELECTED ( Master_Calendar ), Master_Calendar[F Year] = currFY )
            )
        RETURN
            CALCULATE (
                SUM ( Sales[F Net Value_CR] ),
                FILTER ( ALLSELECTED ( Sales ), [Date] IN range ),
                VALUES ( Table[Branch] )
            )
    )

Regards,

Xiaoxin Sheng

[v-shex-msft]

HI @vs_7,

AFAIK, current power bi does not support creating dynamic calculated column/tables based on filter selections.
They work on different data levels and you can't use child level to affect its parent.

For your scenario, I'd like to suggest you extract the year ranges to create an unconnected table and use it to create a slicer as selector, then you can raw table branch and year to create a matrix. (branch to row, year to column)

After these steps, you can write a measure formula to lookup corresponding calculations based on current row and column groups and skip the calculation on not match ranges. (power bi visual will auto-hide the blank field)

formula =
VAR currFY =
    SELECTEDVALUE ( Master_Calendar[F Year] )
VAR selectedFY =
    SELECTEDVALUE ( NewTable[F Year] )
VAR selectedPFY =
    ( VALUE ( LEFT ( selected, 4 ) ) - 1 ) & "-"
        & LEFT ( selected, 4 )
RETURN
    IF (
        currFY IN { selectedFY, selectedPFY },
        VAR range =
            CALCULATETABLE (
                VALUE ( Master_Calendar[Date] ),
                FILTER ( ALLSELECTED ( Master_Calendar ), Master_Calendar[F Year] = currFY )
            )
        RETURN
            CALCULATE (
                SUM ( Sales[F Net Value_CR] ),
                FILTER ( ALLSELECTED ( Sales ), [Date] IN range ),
                VALUES ( Table[Branch] )
            )
    )

Notice: the data level of power bi.

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng