Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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.
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.
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?
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.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |