Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
vs_7
Continued Contributor
Continued Contributor

Dynamic Column Name change Based on Slicer Selections

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
vs_7_0-1652093733585.png

 

 

slicer is below.

 

vs_7_1-1652093733134.png

@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.

 

3 REPLIES 3
vs_7
Continued Contributor
Continued Contributor

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] )
)
)

vs_7_0-1652333004407.png

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.