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 ove...
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

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.

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

      • BhaveshPatel's avatar
        BhaveshPatel
        Icon for Super User rankSuper User
        10 years ago

        Datamodel

        Your Data model