Forum Discussion
Helper IILine Graph with Multiple Months and Averages
Hi cmaloyb ,
Please try to update the formula of measure as below:
NumberofsubA = VAR a=MAX('Table 1'[Turn-in Date (Month)]) VAR b=DIVIDE(CALCULATE(DISTINCTCOUNT('Table 1'[Student ID])), COUNTBLANK('Table 2'[Program Start Date])+CALCULATE(COUNT('Table 2'[Student ID]),FILTER('Table 2',NOT(ISBLANK('Table 2'[Program Start Date]))&&'Table 2'[Program Start Date]<=a)),0) return bHere updated the formula of calculating the number of active students:
1. Calculate the number of program start date which is blank
COUNTBLANK('Table 2'[Program Start Date])2. Calculate the number of program start data which is less than assignment submit date
CALCULATE(COUNT('Table 2'[Student ID]),FILTER('Table 2',NOT(ISBLANK('Table 2'[Program Start Date]))&&'Table 2'[Program Start Date]<=a)3. Add both to get active student numbers
Best Regards
Rena
Hi cmaloyb ,
If I understand correctly , you have two tables : Table 1 and Table 2 . Table 1 contains the student ID and the date which the student submit assignment , and Table 2 contains the student and the date the program was started . It looks like as below:
Your expected result is to get one line chart as below screenshot with data in X axis and the percentage of active students number and the submitted assignments display in "Values" field?
Whether the above understanding is correct? If no, please correct me. And could you please provide some sample data (exclude sensitive data) from this 2 tables with text?
Best Regards
Rena
Helper IIHi Anonymous, that is correct.
Table 1 will have multiple submissions from more than one student whereas Table 2 will only have the student and their respective program start date once (one-to-many relationship). I will provide some example data (non-sensitive) of both Table 1 and 2.
Table 1:
| Unique ID | Student | Student ID | Turn-in Date (Month) |
| T0001 | A | S0001 | 1/3/2019 |
| T0002 | B | S0002 | 1/3/2019 |
| T0003 | C | S0003 | 1/2/2019 |
| T0004 | D | S0004 | 1/6/2019 |
| T0005 | E | S0005 | 1/23/2019 |
| T0006 | F | S0006 | 1/15/2019 |
| T0007 | G | S0007 | 1/9/2019 |
| T0008 | A | S0001 | 2/16/2019 |
| T0009 | D | S0004 | 2/14/2019 |
| T0010 | E | S0005 | 2/28/2019 |
| T0011 | B | S0002 | 2/1/2019 |
| T0012 | G | S0007 | 2/2/2019 |
| T0013 | A | S0001 | 3/1/2019 |
| T0014 | B | S0002 | 3/1/2019 |
| T0015 | C | S0003 | 3/2/2019 |
| T0016 | F | S0006 | 3/3/2019 |
| T0017 | A | S0001 | 4/1/2019 |
| T0018 | B | S0002 | 4/16/2019 |
| T0019 | D | S0004 | 4/20/2019 |
| T0020 | E | S0005 | 4/20/2019 |
| T0021 | F | S0006 | 4/21/2019 |
| T0022 | G | S0007 | 4/22/2019 |
| T0023 | A | S0001 | 5/1/2019 |
| T0024 | B | S0002 | 5/2/2019 |
| T0025 | C | S0003 | 5/3/2019 |
| T0026 | D | S0004 | 5/4/2019 |
| T0027 | E | S0005 | 5/5/2019 |
| T0028 | F | S0006 | 5/6/2019 |
| T0029 | G | S0007 | 5/7/2019 |
| T0030 | B | S0002 | 6/24/2019 |
| T0031 | C | S0003 | 6/28/2019 |
| T0032 | E | S0005 | 6/29/2019 |
| T0033 | G | S0007 | 6/30/2019 |
| T0034 | A | S0001 | 7/12/2019 |
| T0035 | C | S0003 | 7/22/2019 |
| T0036 | G | S0007 | 7/29/2019 |
| T0037 | A | S0001 | 8/12/2019 |
| T0038 | B | S0002 | 8/13/2019 |
| T0039 | C | S0003 | 8/14/2019 |
| T0040 | D | S0004 | 8/15/2019 |
| T0041 | F | S0006 | 8/16/2019 |
Extends to December but didn't seem necessary to provide submissions for all months.
Table 2: (Blanks represent previous start dates prior to 2019)
| Student ID | Program Start Date |
| S0001 | |
| S0002 | |
| S0003 | 3/1/2019 |
| S0004 | |
| S0005 | 4/1/2019 |
| S0006 | |
| S0007 | 10/1/2019 |
As for the line chart, you are absolutely correct. I would like to display each month in the x-axis and in the "Values" field I would like to have the average of submissions per active students on a monthly basis.
For example, in January, I would like to display the value: (7 Submissions)/(4 Active Students) = 1.75 or 175%
So a value for each month on one continuous line graph.
Note: I do need the query as shown above for other graphing purposes.
Thank you for your response!
R/
Connor
Hi cmaloyb ,
You can create one measure as below:
NumberofsubA = VAR a=MAX('Table 1'[Turn-in Date (Month)]) VAR b=CALCULATE(DISTINCTCOUNT('Table 1'[Student ID]))/ CALCULATE(DISTINCTCOUNT('Table 2'[Student ID]),FILTER('Table 2',ISBLANK('Table 2'[Program Start Date])||(YEAR('Table 2'[Program Start Date])=YEAR(A)&&MONTH('Table 2'[Program Start Date])<=MONTH(a)))) return bBest Regards
Rena
Hello @cmaloyb,
You can create a measure as shown below:
NumberofsubA = VAR a=MAX('Table 1'[Turn-in Date (Month)]) VAR b=CALCULATE(DISTINCTCOUNT('Table 1'[Student ID]))/ CALCULATE(DISTINCTCOUNT('Table 2'[Student ID]),FILTER('Table 2',ISBLANK('Table 2'[Program Start Date])|| (YEAR('Table 2'[Program Start Date])=YEAR(A)&&MONTH('Table 2'[Program Start Date])<=MONTH(a)))) return bBest regards
Rena
- cmaloyb
Anonymous ,
Thank you very much, that seems to work. I did find one issue through extending the example. When the year ends (2019 in this case) and the students begin turning in their monthly assignments the following year (2020), those students with Program Start Dates in 2019 (S0003, S0005, S0007) are no longer counted as active students.
Would you mind explaining what dax in the measure means to help me work this out?
Connor
Hi cmaloyb ,
Where did the dates you put on line chart Axis come from? Is it from a calendar table? Or from the field Turn-in Date (Month) in Table 1?
Best Regards
Rena