Forum Discussion

Betsy's avatar
Betsy
Icon for Helper IV rankHelper IV
10 years ago
Solved

CALCULATE/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

 

 

Need Help
  • BhaveshPatel's avatar
    BhaveshPatel
    10 years ago

    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's avatar
    BhaveshPatel
    Icon for Super User rankSuper User
    10 years ago

    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's avatar
      Betsy
      Icon for Helper IV rankHelper IV
      10 years ago

      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's avatar
        BhaveshPatel
        Icon for Super User rankSuper User
        10 years ago

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