Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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 - Microsoft Power BI Community

 

For some reason my table seems to show the numbers incorrectly.  for example in april this year its saying only 30 people are active, but I can see that's not correct by looking at the table.  The number should be more like the Total figure of 700k, as that's (ballpark) the rough number of actives on any given day.

davehsongwriter_2-1649424326856.png

 

My measure (that appears to work) looks like this:

 

CORRECT Active Members =
CALCULATE(COUNTROWS('CE vwContact (2)'),
    FILTER( VALUES('CE vwContact (2)'[Rics_ElectionDate]), 'CE vwContact (2)'[Rics_ElectionDate] <= MAX('-Calendar'[Date])),
    FILTER( VALUES('CE vwContact (2)'[Rics_LapsedDate]), OR('CE vwContact (2)'[Rics_LapsedDate] >= MIN('-Calendar'[Date]),
ISBLANK('CE vwContact (2)'[Rics_LapsedDate]))))
 
The election date and lapsed date ni my vwContact table is linked to the 'Date' field in the Calendar field.
With a one to many relationship from the calendar into the contacts database.
 
Can anyone work out why the numbers aren't showing up correctly?  I want it to show only the contacts that are active on those dates.
 
Thank
1 ACCEPTED SOLUTION

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

View solution in original post

7 REPLIES 7
johnt75
Super User
Super User

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
Not applicable

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)

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
Not applicable

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

davehsongwriter_0-1649428620936.png

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

davehsongwriter_1-1649428677280.png

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

😕

 

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

Anonymous
Not applicable

Ahhh yes that did it!!!  So my final measure code was:
 
CORRECT Active Members =
CALCULATE(COUNTROWS('CE vwContact (2)'),
    'CE vwContact (2)'[Rics_ElectionDate] <= MAX('BI vwCalendar (2)'[Date]),
    OR(
'CE vwContact (2)'[Rics_LapsedDate] >= MIN('BI vwCalendar (2)'[Date]),
ISBLANK('CE vwContact (2)'[Rics_LapsedDate])),
REMOVEFILTERS('CE vwContact (2)')
)
 
I'm gonna need to check the numbers but that appears to have done it!! many thanks!
Anonymous
Not applicable

Looking at the numbers it looks like the contacts are only being counted once, in the first date they appear... but they should be counted on every date that they're active.  I'm not sure why this isn't happening because I think my measure is correct?  I don't have a distinct count in it...?

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.