Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

 

 

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.

 

Need Help
Tips and Tricks

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    HI Anonymous,

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      HI Anonymous,

      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