Optimization of Aggregation Measure Where Status Dates May be Years Apart

[ctaylor]

Hi!

I have a measure that is running on a unit history table that is attempting to calculate values on a yearly basis by finding the status of the unit at the end of the year, or current date.  I work for a property management company and since this is a unit history table, the status may not have updated within the year or even in the past 5 years.  I have a working solution, and when populating by pre-made ownership groupings the load time is acceptable.  When trying to drill down into the groups the load time becomes closer to a minute to populate.  Therefor I am looking for some help to redefine the measure to speed it up.

Ill paste some information below, let me know if anyone needs any additional info and I'll provide what I am able to.

Here is the current formula:

Total Unit Count = 
    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'),
            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"})
            )
        ),
        FILTER(ALL('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date]))
    )

Here are the relationships. 

The current output:

[v-jayw-msft · Accepted solution]

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.

[ctaylor]

Thanks @v-jayw-msft 

That's great and it did cut the load time by almost half which is awesome!

It's still lenghty to load the visual when expanding the row though.

I realize that I am applying a lot of filters but is there any other way you can think of to speed this up to maybe get it down to about the 15 second range?

Original measureSuggested measure

[v-jayw-msft]

Sorry @ctaylor ,

I couldn't see more obvious optimization in syntax and I'm not sure i can change it in logic without affecting the end result.

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.

[ctaylor]

@v-jayw-msft 

Thanks for taking the time to look at this. I have accepted your answer as the solution and I think I know another spot in a different report that I can use this same logic!