Row context not overriding in calculate function

[bobby27]

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

[kentyler]

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.

[bobby27]

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]

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

[parry2k]

@bobby27 something obvious is missing, share pbix file wiht sample data, remove sensitive info before sharing. you can also send it by email, my email is in the signature.

[bobby27 · Accepted solution]

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.

[parry2k]

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

[bobby27]

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.