Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
slicer is below.
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.
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:
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
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
104 | |
98 | |
97 | |
38 | |
37 |
User | Count |
---|---|
152 | |
121 | |
73 | |
71 | |
63 |