In a health insurance database, we have Members who may or may not have Claims. I have a parameter on a slider that is "Limit Number of Months", an integer, between 1 and 24. It limits my dashboard to Claims with a Service Date in the last however-many months.
So far so good. I have a handful of charts with various dimensions that all filter by this successfully.
Now, I want to add a chart with the number of Members -- COUNTD(MEMBER_ID) -- in a stacked bar arrangement where members WITH a claim in the parameterized time period get one color (we call this "Utilization") and members WITHOUT a claim in the time period get another ("No Utilization").
Here's the calculation for "Utilization Flag" (it was a boolean, but ignore that for now):
IF ISNULL(MAX([Service Date])) THEN 'No Utilization'
ELSEIF MAX([Service Date]) >= DATEADD('month', [Limit Number of Months]*-1,TODAY()) THEN 'Utilization'
ELSE 'No Utilization'
MAX() forces this to be a Measure, not a dimension, which I understand, but if I take the MAX() out, then I have a situation where some Members are double counted, since the [Service Date] is tied to the Claim. That is, a Member can have a Claim in the service period and outside of it, so they would be double counted if I counted distinct MEMBER_IDs grouping by this Utilization Flag.
So I need the MAX(), since if the most recent claim wasn't in the period, then the older ones don't matter. Now, though, I can't stack the bars because my boolean result is now a Measure.
The best I can do is to set the Level of Detail to MEMBER_ID, and make Utilization a column header. This works visually (except I'd prefer a true stacked bar), and the numbers are correct, but now if I mouse over it selects every individual member... I have a uni-color stacked bar with thousands of unit-width stacks (note the high number of marks in the screenshot)! Also, this makes it impossible to calculate and display the "total" number of members.
Lastly, I'm reticent to add another subquery to the process, although my one possibility is to add "latest activity" as a field in the database, but I feel like I shouldn't have to. The dashboard is filter and drill heavy, so having a single data source is a huge boon.
I can't publish data due to privacy concerns, but I've included a highly aggregated screenshot.
Any ideas are welcome!