Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AlB
Super User
Super User

Unexpected behavior in Power BI vs expected in Power Pivot - Context transition

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

DateYearIdSchoolIdRankCol
30/09/2017201711
01/10/2017201711
02/10/2017201711
03/10/2017201711
04/10/2017201711
30/10/2018201811
31/10/2018201811
01/11/2018201811
02/11/2018201811
30/01/2017201721
31/01/2017201721

 

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

DateYearIdSchoolIdRankCol
30/09/2017201715
01/10/2017201711
02/10/2017201712
03/10/2017201713
04/10/2017201714
30/10/2018201813
31/10/2018201814
01/11/2018201811
02/11/2018201812
30/01/2017201721
31/01/2017201722

 

 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

 

 

2 ACCEPTED SOLUTIONS

Just disable Auto Date/Time.

(explanation would be long, but really - just don't use Auto Date/Time and create your own Date table).

 

View solution in original post

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.

 

View solution in original post

9 REPLIES 9
v-juanli-msft
Community Support
Community Support

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
)

3.png

 

while the rank column in your table3 is like:

4.png

 

Best Regards

Maggie 

Hi @v-juanli-msft

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Just disable Auto Date/Time.

(explanation would be long, but really - just don't use Auto Date/Time and create your own Date table).

 

@marcorusso

 

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.

 

@marcorusso

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.