I have a table that has 62 million records. I created a concatenated field, Member Months Counter. See column A below so that I could total the number of members per each enrollment period in a chart. Each Member can have as many as 48 enrollment periods going all the way back to Jan 2012. So, I might have a total of 200 distinct members in one of my groups but there would be as many as 9600 enrollment records. I have to use Count(distinct) because of column C. Each member may have up to 5 different Claims_BenTypeCd. Previously, I had a filter to only show the Claims_BenTypeCd=M and I only needed to use the count function.. However, there are some members that do not have M, but only have 9. So, I am now using Count(Distinct) to get an accurate total of the members per month. It is taking 45 secs to 2 minutes to render the chart now that I have made the change to Count(Distinct)
My goal is just to get an expression total so that it can be used to calculate an average.
Here is a shortened version of my expression and a sample of the top 25 rows of my chart.
Thanks for your reply. The second solution would be static and unfortunately not updated as my users made selections in the data model. So, we do need a solution that counts or sums. I could see making a master table with a counter field...
I have tried your aggr command and can not get it to work. It returns no result.
Okay, so now I get the total for MemberMonthCounts and it renders much faster. However, in my straight table, I only get the first month in my enrollment period. Is there a quick way to get an aggregate by Enrollment period which will add up to the total on the top row? Say Enrollment Period is now the dimension in the straight table.