This yields an incorrect result. It seems like context transition is ignoring the ALL(Table1[Date]) in CALCULATETABLE. If I run exactly the same in Power Pivot I get the expected results, i.e., a ranking by date within each YearId-SchoolId pair. The Date column is of type date.
Now, we also have a Table3 with data values and code for the calculated column exactly the same as in Table1. The only difference with Table1 is that Date is of type text. Interestingly enough, RankCol yields the correct result in this case and context transition seems to be working as it should.
The date type should play no role here. It doesn't in Power Pivot.
You can see as well that I have created a Table2 in the query editor by duplicating another query. Table2 appears empty in the Data view while it shows the data in the query editor. In fact, the query editor seems to be acting a bit crazy today like ignoring table name changes.
Thanks very much for your reply. A couple of things:
1. Why do you say the result in RankCol in Table3 is incorrect? I believe it is correct. Bear in mind that the Date column is of type text in this case so the RANKX is actually performing an alphabetical sorting and thus 01/10/2017 is ranked higher than 30/09/2017 in an ascending ranking. That's exactly what I expected, plus the context transition seems to work correctly. Disagree?
2. I appreciate the alternative you provide but I am especially interested in, and that is the question on my post, why RankCol in Table1 does not work. It should, based on the reasons I presented earlier, and it fact it does work in Power Pivot with exactly the same data. Why doesn't it in Power BI Desktop? The result should be the same as what you obtain in your 'Column 2'