Forum Discussion
DAX Optimization Help Needed - Measure That Calculates On Each Date Point in a Line Chart
- 1 year ago
You could try creating a summary table which pre-calculates the rate for a given day.
Dynamic Retention Rate Table = ADDCOLUMNS ( CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2024, 12, 31 ) ), "Retention rate", VAR SelectedDate = [Date] VAR SelectedDateMinus367 = SelectedDate - 367 VAR CurrentMembers = CALCULATETABLE ( DISTINCT ( 'Invoice'[membership__c] ), ALL ( 'Invoice' ), 'Invoice'[CreatedDate] <= SelectedDate, 'Invoice'[Expiration_Date__c] >= SelectedDate, 'Invoice'[Membership_Type__c] <> "Trial Membership" ) VAR Members367DaysAgo = CALCULATETABLE ( DISTINCT ( 'Invoice'[membership__c] ), 'Invoice'[CreatedDate] <= SelectedDateMinus367, 'Invoice'[Expiration_Date__c] >= SelectedDateMinus367 ) VAR ActiveBoth = INTERSECT ( CurrentMembers, Members367DaysAgo ) VAR CountActiveBoth = COUNTROWS ( ActiveBoth ) VAR CountMembers367DaysAgo = COUNTROWS ( Members367DaysAgo ) RETURN DIVIDE ( CountActiveBoth, CountMembers367DaysAgo ) )It would take a while to create the table during data refresh, but that would be invisible to end users.
johnt75 This seems like a great solution. I am going to give it a try. One question though. This measure is filterable by other dimension tables and end users are utilizing the filters to look at retention across different dimensions.
If a filter was applied to the original table would it not impact summary table? If so I would need to figure out a solution for that.
I think it would depend on how many dimensions were being used at the same time, and how many distinct values those dimensions have. It would in theory be possible to generate the cross join of all dimensions and every date you are interested in, but that would become a very big table very quickly if you have a either a lot of dimensions or just a few dimensions with high cardinality.
SQLBI have a great article on generating summary tables at https://www.sqlbi.com/articles/computing-accurate-percentages-with-row-level-security-in-power-bi/ . You might find some useful insights there.