Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
I am fairly new to Power BI and need help with creating a trend line/line graph from my data.
My first table, Monthly Turn-In Table tracks when students A through G turn in a monthly assignment. The fields in this table are Unique ID which is self explanatory, Student (A through G), associted ID, and date on when assignment was turned in.
The second table, ActiveGrade, gives a date (or null) for students that have been in the program long enough that their grade will begin to count as credit. For example, Student C has been turning in their monthly assingment since they began the program but come 3/1/2019, the assignment will begin to count as credit.
After putting it into Power BI, I have edited the query to have each month as its own field to count if a student has submitted their monthly assingment. I have also added a field for each month to give a value for those students that are active. Students without ActiveGradeDates were active previous to the beginning of the data.
I merged the ActiveGrade query with the Monthly Turn-in along with grouping and pivoting to achieve the above query.
I can not seem to figure out how to have all 12 months on the x-axis while having a single line graph to connect the 12 different points from the calculation (# of submissions in month)/(# of active students in same month).
I've tried many different approaches and have yet to figure it out.
P.S. I do need the query how it is for other graphing purposes in my report. Sorry for the long message!
Thanks!
Connor
Solved! Go to Solution.
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 b
Here 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
Hi @v-yiruan-msft, 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
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 b
Best regards
Rena
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
Hi @v-yiruan-msft,
The dates on the Axis came from the field Turn-In Date (Month) in Table 1.
R/
Connor
Hi @cmaloyb ,
Please update the formula of 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 b |
And the meaning of the formula as follow:
1. Get the current Turn-in Date
VAR a=MAX('Table 1'[Turn-in Date (Month)])
2. Count the number of the submission
CALCULATE(DISTINCTCOUNT('Table 1'[Student ID]))
3. Count the number of the active student
1) Get the records which the program start date is blank or less than assignment turn in date
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)))
2) Count of the number of the students who satisfy the above conditions
DISTINCTCOUNT('Table 2'[Student ID])
In addition, please find the details of the functions used in formula in following documentations:
https://docs.microsoft.com/en-us/dax/distinctcount-function-dax
https://docs.microsoft.com/en-us/dax/filter-function-dax
Best Regards
Rena
Hi @v-yiruan-msft,
So I added I changed a Program Start Date in Table 2 to test the measure, however, it still does not include those dates that were passed in the previous year.
New Table 2:
Student ID | Program Start Date |
S0001 | |
S0002 | |
S0003 | 3/1/2019 |
S0004 | |
S0005 | 4/1/2019 |
S0006 | 1/2/2020 |
S0007 | 10/1/2019 |
I added more submissions onto Table 1 as well for January 2020 to test the measure.
T0061 | B | S0002 | 1/3/2020 |
T0062 | C | S0003 | 1/18/2020 |
T0063 | D | S0004 | 1/19/2020 |
T00064 | G | S0007 | 1/20/2020 |
With the measure NumberofsubA put into a Line Graph by Month, January 2020 has NumberofsubA = 1.00
Given the 4 submissions in January 2020 with the 3 blank Program Start Dates & 1 Jan. 2020 Program Start Date (4 Program Start Dates), this would be: 4 Submissions/4 Program Start Dates = 1.00.
We are still missing those 3 Program Start Dates from the previous year in 2019 (S0003, S0005 & S0007).
I have tried modifying the given measure, however I'm usually left with an error or a calculation that is very far from correct.
Any ideas?
I would like to thank you so much for your help so far! It has been wonderful learning!
R/
Connor
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 b
Here 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
I've run into a requirement change where those who were submitting their monthly assignments will not count in the numerator until their program start date is met.
How do I include this filter in the numerator? I tried using the filtering method used in the denominator, however it does not return the correct results.
R/
Connor
Hi @v-yiruan-msft,
So I added I changed a Program Start Date in Table 2 to test the measure, however, it still does not include those dates that were passed in the previous year.
New Table 2:
Student ID | Program Start Date |
S0001 | |
S0002 | |
S0003 | 3/1/2019 |
S0004 | |
S0005 | 4/1/2019 |
S0006 | 1/2/2020 |
S0007 | 10/1/2019 |
I added more submissions onto Table 1 as well for January 2020 to test the measure.
T0061 | B | S0002 | 1/3/2020 |
T0062 | C | S0003 | 1/18/2020 |
T0063 | D | S0004 | 1/19/2020 |
T00064 | G | S0007 | 1/20/2020 |
With the measure NumberofsubA put into a Line Graph by Month, January 2020 has NumberofsubA = 1.00
Given the 4 submissions in January 2020 with the 3 blank Program Start Dates & 1 Jan. 2020 Program Start Date (4 Program Start Dates), this would be: 4 Submissions/4 Program Start Dates = 1.00.
We are still missing those 3 Program Start Dates from the previous year in 2019 (S0003, S0005 & S0007).
I have tried modifying the given measure, however I'm usually left with an error or a calculation that is very far from correct.
Any ideas?
I would like to thank you so much for your help so far! It has been wonderful learning!
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 b
Best Regards
Rena
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |