Forum Discussion

cmaloyb's avatar
cmaloyb
Icon for Helper II rankHelper II
6 years ago
Solved

Filtering Numerator of Measure with Changing Dates.

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's avatar
    Anonymous
    6 years ago

    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable

    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's avatar
      cmaloyb
      Icon for Helper II rankHelper II

      Hi Anonymous,

       

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

       

      Respectfully,

      Connor