Forum Discussion

PhilippeGR's avatar
PhilippeGR
Frequent Visitor
1 year ago
Solved

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]), AL...
  • johnt75's avatar
    johnt75
    1 year ago

    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
    Result

    and make the corresponding changes in [Value MaxYR-1] as well.

     

johnt75's avatar
johnt75
Icon for Super User rankSuper User
1 year ago

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
    Result

and make the corresponding changes in [Value MaxYR-1] as well.