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:
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.
My measure (that appears to work) looks like this:
Solved! Go to Solution.
I'm guessing that the relationship between calendar table and election date is active, and that is causing the filtering to the first occurence.
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] ) )
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.
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)....
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...?
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.