Forum Discussion
Helper IVCALCULATE/COUNTROWS and date filter issue
Hi!
I have a complicated model, where one of the things I have calculated is the number of times a student says "stop" to opt out of our service. What I need to do now is look at these stops over the last 30 days, last 14 days, and last 24 hours. These filters work for other measures (# of responses, unique respondents) but not stops.
Sometimes they don't actually say stop, so I use the fact that they have been turned off (Active=FALSE) and that they sent a text message without an ID number attached (Package ID ="0") to calculate stop outs from two imported CSV files.
Stop Outs = CALCULATE(COUNTROWS('student_export'),'student_export'[active]=FALSE,'communicationresponse_export'[Package ID]="0")
To create the date filters, I made a date table, then calculated columns using syntax I found here for someone else's question. The filters themselves seem to work fine. The date table has a relationship with the communicationresponse table that the Package ID comes in the Stop Outs syntax above, but not with the student export table (the only dates in that table are when the student became active and when they expire, which I don't want the filters for). When I apply the date filters, the stop outs are undercounted.
Should I use something else to get stop outs, aside from CALCULATE(COUNTROWS)? I'm at a loss.
Thanks!
Betsy
calc1= CALCULATE([totalrows],Student_export[Active]="FALSE",FILTER(comresponse_export,comresponse_export[Package ID]="0"))
totalrows = COUNTROWS('student_export')
calc2= CALCULATE([totalrows],Student_export[Active]="FALSE",FILTER(comresponse_export,comresponse_export[Package ID]="0"&& comresponse_export[Text]="Stop"))
You can use filter instead of directly passing on the filtermore filters
13 Replies
- BhaveshPatel
Super User
Sometimes they don't actually say stop, so I use the fact that they have been turned off (Active=FALSE) and that they sent a text message without an ID number attached (Package ID ="0") to calculate stop outs from two imported CSV files.
Confusing. What are you trying to achieve and how does your dataset look like.
BetsyBhaveshPatel Thanks for the reply!
Data looks something like this:
Table 1. Student_export (Student Info)
Student ID Reg. Date Exp. Date Active
1 1/1/2016 1/1/2017 TRUE
2 1/1/2016 6/30/2016 TRUE
3 5/1/2016 1/1/2017 FALSE
Table 2. communicationresponse_export (Response info)
Student ID Text Send Date Package ID Package Date
1 Yes 1/5/2016 1 1/5/2016
1 5 2/10/2015 7 2/9/2016
3 Stop 5/1/2016 0 5/1/2016
In the raw data, the 0 for Package ID was a null value (stops aren't counted as "responses" to actual questions), which I replaced as 0, with the Package IDs transformed from whole numbers into text values.
So I count rows with a filter set on active = FALSE and Package ID = "0".
Stop Outs = CALCULATE(COUNTROWS('student_export'),'student_export'[active]=FALSE,'communicationresponse_export'[Package ID]="0")
Does that help?
- BhaveshPatel
Thanks for the info. Working on your solution. will keep you posted further.