Forum Discussion
How do you dynamically calculate value based on slicer
Please bear in mind that I am fairly new at DAX.
I have a table called BaseTable as follows
and a slicer
I have defined 4 measures:
[MaxYr] = CALCULATE(max('BaseTable'[UWYear]), ALLSELECTED('BaseTable'))
[Value MaxYR] = CALCULATE([Value], FILTER(ALL('BaseTable'), 'BaseTable'[UWYear] = [MaxYr]))
[MaxYr-1] = [MaxYr] - 1
[Value MaxYR-1] = CALCULATE([Value], FILTER(ALL('BaseTable'), 'BaseTable'[UWYear] = [MaxYr-1]))
[MaxYR] gives me the max UWYear from the Slicer.
When I choose a UWYear value of 2025, I get the following
Which is correct
However, I change a UWYear value to 2024, I get the following
Which is not correct.
I was expecting [Value MaxYR] to be $1,177 and [Value MaxYR-1] to be BLANK.
Can you please help?
The issue is the ALL('BaseTable') in the FILTER arguments. That makes every year visible, and so when you call ALLSELECTED that will also return all the years.
Also, it is bad practice to filter entire tables, you should only filter the columns you need to. It is more accurate and more efficient.
Change your [Value MaxYr] function to
Value MaxYR = VAR MaxYear = [MaxYr] VAR Result = CALCULATE ( [Value], 'BaseTable'[UWYear] = MaxYear ) RETURN Resultand make the corresponding changes in [Value MaxYR-1] as well.
1 Reply
johnt75Super User
The issue is the ALL('BaseTable') in the FILTER arguments. That makes every year visible, and so when you call ALLSELECTED that will also return all the years.
Also, it is bad practice to filter entire tables, you should only filter the columns you need to. It is more accurate and more efficient.
Change your [Value MaxYr] function to
Value MaxYR = VAR MaxYear = [MaxYr] VAR Result = CALCULATE ( [Value], 'BaseTable'[UWYear] = MaxYear ) RETURN Resultand make the corresponding changes in [Value MaxYR-1] as well.