cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ctaylor Frequent Visitor
Frequent Visitor

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

Accepted Solutions
v-jayw-msft Senior Member
Senior Member

Re: 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.

View solution in original post

4 REPLIES 4
v-jayw-msft Senior Member
Senior Member

Re: 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.

View solution in original post

ctaylor Frequent Visitor
Frequent Visitor

Re: Optimization of Aggregation Measure Where Status Dates May be Years Apart

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?

 

old measure.PNGOriginal measurenew measure.PNGSuggested measure

v-jayw-msft Senior Member
Senior Member

Re: Optimization of Aggregation Measure Where Status Dates May be Years Apart

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 Frequent Visitor
Frequent Visitor

Re: Optimization of Aggregation Measure Where Status Dates May be Years Apart

@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
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)