I have a challenge for whoever have a deep understanding of the inner workings of the formular engine. It seems that depending on where the filter is applied (filter pane or column within a visual), the performance is very differnt and the queryplan completely changes.
The measure is rather "simple" - For whatever period the users selects, calculate the Age in Years of the "Product" at the min(date) of the selection. Afterwards filter out only those ages that are selected.... Below query solves the problem, but the challenge is when it's over millions of rows and the period needs to be in the colum of the visual (ex. number of product at a certain age pr, month)
Number of products =
VAR MinDate = MIN ( 'CalendarDate'[Date] )
VAR Calc =
CALCULATE (
COUNTROWS ( 'Product' ),
'Product'[DW_ValidFrom] <= MinDate,
'Product'[DW_ValidTo] >= MinDate,
all ('CalendarDate'),
FILTER (
'Product',
VAR AgeCalculated =
IF (
'Product'[PurchaseDate] <= MinDate,
TRUNC(YEARFRAC( 'Product'[PurchaseDate], MinDate ) )
)
RETURN
CONTAINS ( VALUES ( 'Age'[AgeYearINT] ), 'Age'[AgeYearINT], AgeCalculated )
)
)
RETURN
Calc
Samplefile can be found here: SCD2 Age calc.pbix
Problem is also described more in detail here:
https://community.powerbi.com/t5/Desktop/Calculating-point-in-time-age-of-persons-and-filtering-them...
Challenge is to get the left visual to perform better (so that multiple periods can be added) - and remove all the unneccesary calculations as found in the Server timings.
