Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear all,
I came across some odd behaviour in Power BI Desktop. The query editor has been acting strangely as well. You can get the file here. Let's see if you can reproduce it. We have Table1 as follows:
Table1
Date | YearId | SchoolId | RankCol |
30/09/2017 | 2017 | 1 | 1 |
01/10/2017 | 2017 | 1 | 1 |
02/10/2017 | 2017 | 1 | 1 |
03/10/2017 | 2017 | 1 | 1 |
04/10/2017 | 2017 | 1 | 1 |
30/10/2018 | 2018 | 1 | 1 |
31/10/2018 | 2018 | 1 | 1 |
01/11/2018 | 2018 | 1 | 1 |
02/11/2018 | 2018 | 1 | 1 |
30/01/2017 | 2017 | 2 | 1 |
31/01/2017 | 2017 | 2 | 1 |
where Rankcol is:
RankCol = RANKX(CALCULATETABLE(Table1;ALL(Table1[Date]));Table1[Date];;ASC)
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.
Table3
Date | YearId | SchoolId | RankCol |
30/09/2017 | 2017 | 1 | 5 |
01/10/2017 | 2017 | 1 | 1 |
02/10/2017 | 2017 | 1 | 2 |
03/10/2017 | 2017 | 1 | 3 |
04/10/2017 | 2017 | 1 | 4 |
30/10/2018 | 2018 | 1 | 3 |
31/10/2018 | 2018 | 1 | 4 |
01/11/2018 | 2018 | 1 | 1 |
02/11/2018 | 2018 | 1 | 2 |
30/01/2017 | 2017 | 2 | 1 |
31/01/2017 | 2017 | 2 | 2 |
where:
RankCol = RANKX(CALCULATETABLE(Table3;ALL(Table3[Date]));Table3[Date];;ASC)
Any idea what is going on?
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 time
Solved! Go to Solution.
Just disable Auto Date/Time.
(explanation would be long, but really - just don't use Auto Date/Time and create your own Date table).
Options and settings / Options / Data Load / Time intelligence / Auto Date/Time
You have that setting enabled and it creates a date table for each date column in the model.
Hi @AlB
The date format is recognized as month/day/year in power bi by default.
Based on my test, the second formula is also incorrect.
i create a rank column
in table1
Column 2 =
RANKX (
FILTER (
ALL ( Table1 ),
[YearId] = EARLIER ( Table1[YearId] )
&& [SchoolId] = EARLIER ( Table1[SchoolId] )
),
Table1[Date],
,
ASC
)
while the rank column in your table3 is like:
Best Regards
Maggie
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'
Thanks a lot
Alrite. guess it's time to call the cavalry
@Zubair_Muhammad, @Greg_Deckler, @MFelix any idea?
Many thanks
This seems more like an issue for @marcorusso, he loves these down and dirty DAX questions.
Just disable Auto Date/Time.
(explanation would be long, but really - just don't use Auto Date/Time and create your own Date table).
What do you mean by disable Auto DateTime? Where?
I don't even have a date table in this case, nor would I actually need one. Just a table with some dates in a column.
Thanks
Options and settings / Options / Data Load / Time intelligence / Auto Date/Time
You have that setting enabled and it creates a date table for each date column in the model.
I see. Thanks very much. I didn't even know that option.
I knew about creating your own date table, which I do, but not that one is created in the shadow anyway by default.
If you can point me to some resources that deal with the topic (maybe one of your articles?) I'd certainly appreciate it.
Many thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |