cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User III
Super User III

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

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

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

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 III
Super User III

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 III
Super User III

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 IV
Super User IV

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.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

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 III
Super User III

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

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

Super User III
Super User III

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

Highlighted

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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors