Forum Discussion

bobby27's avatar
bobby27
Frequent Visitor
6 years ago
Solved

Row context not overriding in calculate function

  I have created a measure using the calculate function, it does not override the row context of the Attainment and always just returns the row total (see left hand table in linked page) I'm just st...
Need Help
  • bobby27's avatar
    bobby27
    6 years ago

    Hi Parvinder, Ken,

     

    I have solved my problem, It was similar to this problem here https://www.sqlbi.com/daxpuzzle/unexpected-filter-behavior-in-calculate/ and I sorted it by adding an explicit all to the calculate function for the attainment index column I was using to sort the attainment:

     

    CALCULATE([Pupil Count],Sheet1[Attainment]<>"No Data",ALL(Sheet1[Attainment_Index]))

     

    Thanks for your time.

kentyler's avatar
kentyler
Icon for Solution Sage rankSolution Sage
6 years ago

To override the filter context for the row you will need to add an All to your calculate

TotalAttainment = CALCULATE([Pupil Count],ALL(Sheet1[Pupil Count]),Sheet1[Attainment]<>"No Data")
There are many resources for learning. I would recommend https://www.sqlbi.com/   they explain EVERYTHING. Power BI looks sort of like Excel or SQL queries, but that is an illusion. It has its own rules. They are not that hard to learn, but, for me at least, it was hard to wrap my head around a whole different way of approaching data.

 

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

bobby27 What is your table relationship? What is pupil count measure? your measure should work, need more info to further debug it.

  • bobby27's avatar
    bobby27
    Frequent Visitor
    6 years ago

    Thanks for your post, Pupil Count is COUNTA([PupilUPN]) (with UPN being a ID field for pupil), there aren't any table joins in PBI, since I'm just passing a SQL statement that returns the table. There are no other filters etc, and 404 records is the full amount in the table after records were filtered in query editor.

     

    So left table:

    Attainment conditional column defined in query editor

    Pupil Count = COUNTA([PupilUPN])

    TotalAttainment = CALCULATE([Pupil Count],Sheet1[Attainment]<>"No Data")

     

    Right table: 

    Grade - straight from dataset item DL_Result

    Pupil Count = COUNTA([PupilUPN])

    TotalGrades = CALCULATE([Pupil Count],Sheet1[DLResult]<>"No Data")
     
    Any help would be greatly appreciated.