I'm working on a membership dashboard where I have a date dimension table, a member dimension table and a membership transaction table. The membership transactions have a start and end date field based on the amount of time the member subscribed for. I've been able to accurately calculate a measure which shows a running total of how many active memberships there are however, in so doing, it seems like my slicers no longer work. I've been able to program in a parameter for one dimension I want to filter on but I don't really want to be building out parameters for each dimension I want to filter on.
Is there a better way I can calculate this which will still allow me to use slicers?
This is my current DAX measure, the purple is the component I added to allow for reporting on gender, however, using this method means stacked bar/area charts don't work as expected.
Active Membership = CALCULATE(
DISTINCTCOUNT('DIM Member'[MemberNum]),
FILTER(
ALL('FACT Member Transaction'),
'FACT Member Transaction'[EffectiveDate] <= MAX('Date'[DATE])
&& 'FACT Member Transaction'[ExpirationDate] > MIN('Date'[DATE])
)
,'DIM Member'[Gender] = SELECTEDVALUE('_Parameter Gender'[_Parameter Gender Fields])
)
