cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ctaylor
Helper III
Helper III

Optimization of Aggregation Measure Where Status Dates May be Years Apart

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. 

Relationship.PNG

 

 

 

 

 

 

 

 

 

 

 

 

The current output:

Output.png

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

4 REPLIES 4
v-jayw-msft
Community Support
Community Support

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

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 measureOriginal measureSuggested measureSuggested measure

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

@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!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors