Forum Discussion
Using Dynamic Segmentation and retaining the segments for other measures without recalculating
I was able to resolve this on my own after further research.
To precalculate which counties qualify for a given measure/percentile combo, I used a calculated table using SQLBI's transition matrix strategy. Starting with a list of 3,000 counties, I used GENERATE to create the cartesian product of all possible filter measures used by dynamic segmentation as well as all possible percentile ranges, ending up with calcalated table of 120,000+ rows. I added a calculated column to this new table that returns 1 if county-measure combo fell within the percentile range for that row and 0 if not.
Next, I re-wrote each original measure (i.e. Unemp Rate) to only calculate for the rows in the calculated table that met the measure-percentile combo as per below:Selected Measure, Filtered by Pctile = CALCULATE ( [Selected Measure] ,FILTER( FactJobData ,CONTAINS( FILTER( 'TransitionTable_CountiesPrecalculatedByPercentile' ,'TransitionTable_CountiesPrecalculatedByPercentile'[Measure Sort] = VALUES('Percentile Measures'[Sort]) && 'TransitionTable_CountiesPrecalculatedByPercentile'[Percentile Sort] = VALUES('Percentile Ranges'[Sort]) && 'TransitionTable_CountiesPrecalculatedByPercentile'[In Percentile Range?] = 1 ) ,'TransitionTable_CountiesPrecalculatedByPercentile'[CountyID], MAX(FactJobData[CountyID]) ) ) )The performance boost was enormous. Previously, each time I'd change the slicers on my dashboard, it would take about 2-4 seconds to refresh the data table visual. When I analyzed the query profiler, 99% of the time was spent on the Formula Engine. Now that most of the pre-calculation was accomplished by the calculated table, changing slicers happens almost instantly. Here's the finished report.
Hi, thanks for taking the time to review my question!
I've looked at KEEPFILTERS before, and per your suggestion I reviewed it again. It didn't provide the optimization needed. My issue isn't that the filters are being dropped, it's that the filtered subset of Counties has to be recalculated for each measure and there's a large performance penalty.
How do I avoid recalculating the dynamic segmentation for qualifying counties for each subsequent measure?
I was able to resolve this on my own after further research.
To precalculate which counties qualify for a given measure/percentile combo, I used a calculated table using SQLBI's transition matrix strategy. Starting with a list of 3,000 counties, I used GENERATE to create the cartesian product of all possible filter measures used by dynamic segmentation as well as all possible percentile ranges, ending up with calcalated table of 120,000+ rows. I added a calculated column to this new table that returns 1 if county-measure combo fell within the percentile range for that row and 0 if not.
Next, I re-wrote each original measure (i.e. Unemp Rate) to only calculate for the rows in the calculated table that met the measure-percentile combo as per below:
Selected Measure, Filtered by Pctile =
CALCULATE (
[Selected Measure]
,FILTER(
FactJobData
,CONTAINS(
FILTER(
'TransitionTable_CountiesPrecalculatedByPercentile'
,'TransitionTable_CountiesPrecalculatedByPercentile'[Measure Sort] = VALUES('Percentile Measures'[Sort])
&& 'TransitionTable_CountiesPrecalculatedByPercentile'[Percentile Sort] = VALUES('Percentile Ranges'[Sort])
&& 'TransitionTable_CountiesPrecalculatedByPercentile'[In Percentile Range?] = 1
)
,'TransitionTable_CountiesPrecalculatedByPercentile'[CountyID], MAX(FactJobData[CountyID])
)
)
)The performance boost was enormous. Previously, each time I'd change the slicers on my dashboard, it would take about 2-4 seconds to refresh the data table visual. When I analyzed the query profiler, 99% of the time was spent on the Formula Engine. Now that most of the pre-calculation was accomplished by the calculated table, changing slicers happens almost instantly. Here's the finished report.