Forum Discussion
CALCULATE/COUNTROWS and date filter issue
- 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 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?
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
- BhaveshPatel10 years ago
Super User
Datamodel
Your Data model
- Betsy10 years ago
Helper IV
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?
- BhaveshPatel10 years ago
Super User
There have to have a relationship between all three tables.
- Betsy10 years ago
Helper IV
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?