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 starting out with Power BI and this is driving me insane I don't understand why this is happening.

 

TotalAttainment = CALCULATE([Pupil Count],Sheet1[Attainment]<>"No Data")
 
When I use another dimension in my table and the same formulas but with the different dimension substitued into them, the row context successfully is overridden like in the right hand table.
 
I have defined Attainment as the only conditional column in my query editor so far, other dimensions just pulled straight through from a SQL query. Does this make a difference? Where can I find out more information about what's happening here?
 

https://imgur.com/a/XGmFe9O

Need Help

7 Replies

  • 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.
    • bobby27's avatar
      bobby27
      Frequent Visitor
      6 years ago

      Hi Ken,

       

      I've been watching (and rewatching) the Advanced DAX video from the SQL BI guys this afternoon. I couldn't get the syntax you suggested to work, the all function wouldn't accept Pupil Count as an argument, maybe it wasn't clear it was a measure rather than a column. I have tried incorporating All into that formula without success, I think I just passed ALL(Sheet1) as the second argument in CALCULATE but it had the same result.

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

        I feel you.

        Could you provide a small set of sample data.

        You can just copy and paste into one of these replies from power bi or excel.

        That may let me give you more specific advice.

        Thanks

        Ken