Filtering Numerator of Measure with Changing Dates.

[cmaloyb]

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 1 records students monthly submissions.

Table 2:

Table 2 displays each student and their start date. Blanks means the student was in the program prior to data collection.

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

[Anonymous · Accepted 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.

[cmaloyb]

Seems to have worked, thanks!

[Anonymous]

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.

[cmaloyb]

Hi @Anonymous,

The relationship is a one to many between Student IDs in Table 2 to Table 1.

Respectfully,

Connor