Optimization of Aggregation Measure Where Status Dates May be Years Apart

Hi ctaylor ,

Looks like there are too many filters in your formula, you could filter "AHO Unit History" first and then use KEEPFILTERS() in formula.

Total Unit Count =
VAR filt =
    FILTER (
        'AHO Unit History',
        AND (
            AND (
                AND (
                    NOT ( 'AHO Unit History'[Unit Status] IN { "Model", "Excluded", "Waitlist" } ),
                    AND (
                        'AHO Unit History'[Status Start] <= CALCULATE ( MAX ( 'Calendar'[Date] ) ),
                        OR (
                            'AHO Unit History'[Status End] = BLANK (),
                            'AHO Unit History'[Status End] > CALCULATE ( MAX ( 'Calendar'[Date] ) )
                        )
                    )
                ),
                AND (
                    OR (
                        'AHO Unit History'[PropertyInactiveDate]
                            > CALCULATE ( MAX ( 'Calendar'[Date] ) ),
                        'AHO Unit History'[PropertyInactiveDate] = BLANK ()
                    ),
                    OR (
                        'AHO Unit History'[YardiGoLiveDate] >= DATE ( 2013, 01, 01 ),
                        NOT ( 'AHO Unit History'[YardiGoLiveDate] = BLANK () )
                    )
                )
            ),
            NOT ( 'AHO Unit History'[sValue]
                IN { "Old # - Affordable Active", "Old # - CMHA Active", "Old # - SR Active" } )
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'AHO Unit History'[Unit HMY] ),
        USERELATIONSHIP ( 'AHO Unit History'[YardiGoLiveDate], 'Calendar'[Date] ),
        CROSSFILTER ( 'AHO Unit History'[PropertyHMY], 'Yardi Property Lists'[PropertyHMY], BOTH ),
        CROSSFILTER ( 'Yardi Property Lists'[PropertyHMY], 'All Properties'[HMY], BOTH ),
        FILTER ( ALL ( 'AHO Unit History' ), KEEPFILTERS ( filt ) ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) )
    )

Best Regards,

Jay

Community Support Team _ Jay Wang

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