Forum Discussion

GVallentgoed's avatar
GVallentgoed
Icon for Helper II rankHelper II
2 years ago
Solved

Goal Blank infinity Error

KPI goal error when calculating previous year.  Trying to display average increase from 2023 - 2024. When calculating the average of 2023, get blank infinity error. Need to fix DAX to only recognize ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi GVallentgoed 

     

    Actually, slicers can not affect calculated columns because they're at different levels.

    Slicers are on the report view and calculated columns are on the table view. Slicer can only filter on visuals on report view. The value of a calculated table or calculate column is computed during data refresh, it does not depend on user interaction in the report. 

     

    Consider writing a measure and create a table visual in the report view:

    _2023 PLT =
VAR _currentRowYear =
    YEAR ( MAX ( 'TLO (2)'[Latest Pickup] ) )
RETURN
    IF (
        _currentRowYear = 2023,
        CALCULATE (
            AVERAGE ( 'TLO (2)'[PLT] ),
            FILTER (
                ALLSELECTED ( 'TLO (2)' ),
                YEAR ( 'TLO (2)'[Latest Pickup] ) = _currentRowYear
            )
        )
    )

     

     

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

GVallentgoed's avatar
GVallentgoed
Icon for Helper II rankHelper II
2 years ago

This solves the blank infinity problem, however, it doesnt work with my filters. The 2023 average should change based on filter.  What needs to be added to formula?

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi GVallentgoed 

 

Actually, slicers can not affect calculated columns because they're at different levels.

Slicers are on the report view and calculated columns are on the table view. Slicer can only filter on visuals on report view. The value of a calculated table or calculate column is computed during data refresh, it does not depend on user interaction in the report. 

 

Consider writing a measure and create a table visual in the report view:

_2023 PLT =
VAR _currentRowYear =
    YEAR ( MAX ( 'TLO (2)'[Latest Pickup] ) )
RETURN
    IF (
        _currentRowYear = 2023,
        CALCULATE (
            AVERAGE ( 'TLO (2)'[PLT] ),
            FILTER (
                ALLSELECTED ( 'TLO (2)' ),
                YEAR ( 'TLO (2)'[Latest Pickup] ) = _currentRowYear
            )
        )
    )

 

 

 

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.