Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Active Contacts Over time.

Hi there.  I'm having an issue plotting a graph of number of active members over time.  Its very similar to this topic, but they're getting different results: Solved: Active Clients over time - Mic...
Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    Delete the first reference to election date on line 3, you only need to compare that to the MAX.

johnt75's avatar
johnt75
Super User
4 years ago

I'm guessing that the relationship between calendar table and election date is active, and that is causing the filtering to the first occurence.

Try

CORRECT Active Members =
CALCULATE (
    COUNTROWS ( 'CE vwContact (2)' ),
    'CE vwContact (2)'[Rics_ElectionDate] <= MAX ( '-Calendar'[Date] ),
    OR (
        'CE vwContact (2)'[Rics_LapsedDate] >= MIN ( '-Calendar'[Date] ),
        ISBLANK ( 'CE vwContact (2)'[Rics_LapsedDate] )
    ),
    REMOVEFILTERS ( '-Calendar'[Date] )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I've got 2 connectsionfr from the calendar into the contact,

    One on Date (from the Calendar table) into Lapsed Date in the Contacts table (this is active)

    and the other from Date (calendar table) into election date in the contacts table (this isn't active as it wont let me have 2 active connections)

    • johnt75's avatar
      johnt75
      Super User
      4 years ago

      You need to add the REMOVEFILTERS as I posted, that will remove the effect of the active relationship with the lapsed date, as that is incorrectly filtering the rows in this instance.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Thanks, I've done that an the measure appears to be working:

        But then the same number apears against all the dates in teh table:

        I would expect this number to change as people are elected (start date) or lapse (end date)....

        😕