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.
In my post, https://community.powerbi.com/t5/Desktop/Line-Graph-with-Multiple-Months-and-Averages/td-p/978828, I needed help creating a measure to calculate the number of monthly submissions of students by those who have passed their program start date (and blanks who have been in the program prior to beginning of table.
In this post, all submissions were considered in the numerator whereas the denominator did not count those that did not pass program start date. Once a students program start date happened, they would automatically be counted in the denominator.
After a requirement change, the numerator no longer can count submissions of those who have not reached their program start date. The measure that was used in the previous requirement can be found 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
I will provide examples of the tables that are used.
Table 1:
Table 2:
I've tried filtering the numerator like the denominator is filtered in the measure above, but haven't had any luck. Could someone point me in the right direction?
R/
Connor
Solved! Go to Solution.
Hi @cmaloyb ,
You could use the below formula as numerator but it will work only when the relationship is not active.
Measure = CALCULATE(DISTINCTCOUNT(Table1[Student]),FILTER(Table2,Table2[Start]=BLANK()||Table2[Start]<SELECTEDVALUE(Table1[Turn_in])))
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @cmaloyb ,
You could use the below formula as numerator but it will work only when the relationship is not active.
Measure = CALCULATE(DISTINCTCOUNT(Table1[Student]),FILTER(Table2,Table2[Start]=BLANK()||Table2[Start]<SELECTEDVALUE(Table1[Turn_in])))
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Seems to have worked, thanks!
Hi @cmaloyb ,
Is there any relationship between these two tables?
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-jayw-msft,
The relationship is a one to many between Student IDs in Table 2 to Table 1.
Respectfully,
Connor
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 |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |