Forum Discussion

dwel0120's avatar
dwel0120
Helper III
5 years ago

Counting Columns throughout table Specific Values

Goal: figure out which student IDs have data for all four years (15/16-18/19), last three years (16/17-18/19), and last two years (17/18 and 18/19) by subject (MTH or ELA). I created new columns -

MTH1516Yr = IF(not(isblank('STPTableYear'[AvgCalculatedNorm])) && STPTableYear[Subject] = "Mth" && STPTableYear[Yr] = "1516", 1) for each subject and year (Yr). I'm not sure how to go about doing this. 

 

 

 

Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    dwel0120 , if this true for all 4 years

     

    new column =
    var _cnt = calculate(distinctcount(Table[Yr]), filter('STPTableYear', STPTableYear[Student ID] = earlier(Student ID]) not(isblank('STPTableYear'[AvgCalculatedNorm])) && STPTableYear[Subject] = "Mth" && STPTableYear[Yr] = "1516"))
    return
    if(_cnt >=4,1,0)

     

     

    • dwel0120's avatar
      dwel0120
      Helper III
      5 years ago

      amitchandak The syntax for 'not' is incorrect. It isn't letting me use not isblank. This happened to me earlier when I tried before I posted.