CALCULATE/COUNTROWS and date filter issue

[Betsy]

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

[BhaveshPatel]

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.

[Betsy]

@BhaveshPatel 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 · Accepted solution]

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

[Betsy]

@BhaveshPatel

I think I just need calc1 to get the stop outs, but I must be doing something wrong because the calculation is coming up as blank, even without one of the date filters on.

[Student Rows] = COUNTROWS('Student_export')

Stop Out = CALCULATE([Student Rows],'student_export'[active]=FALSE,FILTER('communicationresponse_export','communicationresponse_export'[Package ID] ="0"))

Am I missing a parenthesis or something?

[BhaveshPatel]

There have to have a relationship between all three tables.

[Betsy]

As far as the relationships are concerned, I have a direct between student_export and communicationresponse_export, and communicationresponse_export and date table.

If I set a relationship between student_export and date table, what is my join? Reg. Date and Exp. Date are both (usually) outside of 30 days, 14 days...etc. Will the students be filtered out based on one of those two dates?

[Betsy]

@BhaveshPatel

I re-arranged the relationships, and took off the page level date filter to get a baseline, and calc1 is still coming up blank. 

[Betsy]

@BhaveshPatel

Is there a way this would work without a direct relationship between the student_export table and the date table? I set the relationships as you suggested, and as I feared, the student rows are being filtered by the date (I guess the rows counted are those that were registered in the last 30 days, or last 14, or last day.

[Betsy]

The latest thing I have tried:

I made a custom column in Student_export listing today's date to link to the date table. All the student rows are now counted, but date filters aren't working at all for my original stop out calculation, or response count (which used to work). I'm getting grand totals instead.

The calc1 column still comes up blank, no matter what else I do, so that calulation I think I have an error there.

Could I do some sort of Student Row ALL() type thing to negate the date filter's impact on student row count?

I'm getting afraid I'm not going to be able to back out of all this.

[Betsy]

I've gone back, and looking at the relationship model here, do I need to create an indirect relationship with every date column in my data for those two tables (student_export) and (communicationresponse_export)? I have birthdays in the student_export and other dates in communicationresponse.

[BhaveshPatel]

Hi Betsy,

You can have a look at my model here.

https://drive.google.com/a/excelpbianalytics.com/file/d/0B5-C_3XrFPdOQTZRQW1xRnFMNVE/view?usp=sharin...

Hope this would solve your issue. 

[BhaveshPatel]

Datamodel

Your Data model

[BhaveshPatel]

Thanks for the info. Working on your solution. will keep you posted further.