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

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

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

        There have to have a relationship between all three tables.