cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

Accepted Solutions
marcorusso Member
Member

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

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

marcorusso Member
Member

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

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
Community Support Team
Community Support Team

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

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 

Super User
Super User

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

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

Super User
Super User

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

 

Alrite.  guess it's time to call the cavalry

@Zubair_Muhammad, @Greg_Deckler, @MFelix  any idea?

Many thanks

Super User
Super User

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

This seems more like an issue for @marcorusso, he loves these down and dirty DAX questions.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

marcorusso Member
Member

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

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

Super User
Super User

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

@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

marcorusso Member
Member

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

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

Highlighted
Super User
Super User

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

@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

marcorusso Member
Member

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 63 members 1,046 guests
Please welcome our newest community members: