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

DAX: Puzzling ALL behaviour in context transition

Hi there,

 

I have the following calculated columns in the 'Product' table of the Contoso data model (available here). 'Product'[ProductKey] has 2517 unique values.

 

A) ‘Product’[Test 1]=CALCULATE(ALL(’Product’[ProductKey])) yields the ProductKey per row.

B) ‘Product’[Test 2]=CALCULATE(COUNTROWS(ALL(’Product’[ProductKey]))) however yields 2517 in all rows (size of the 'Product' table)

 

I find it puzzling that the filter resulting from context transition does affect the result in ‘Product’[Test 1] but it doesn't in ‘Product’[Test 2]. Could someone explain what is going on?

 

Thanks very much

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft Phil_Seamark
Microsoft

Re: DAX: Puzzling ALL behaviour in context transition

Hi @AlB and @MattAllington

 

I did email Jeffrey Wang about this issue recently and he confirmed it is a bug and that it will be fixed in a future release.  I don't have a timeframe, unfortunately.  


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

11 REPLIES 11
Microsoft v-danhe-msft
Microsoft

Re: DAX: Puzzling ALL behaviour in context transition

Hi @AlB,

The sample file you have offered could not be opened due to the license problems coudl you please just share some sample file to have a test?

And the formula you have offered in ‘Product’[Test 1]=CALCULATE(ALL(’Product’[ProductKey])) seemed wrong, if you want to use the CALCULATE function, you should have an Aggregate function in it, could you please modify your formula and test it again?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User III
Super User III

Re: DAX: Puzzling ALL behaviour in context transition

Hi @v-danhe-msft

Here's another file.

There is no error. Both 'Test 1' and 'Test 2' work as you can see in the attached file. I am just trying to understand why the interaction between context transition and ALL() seems to be different in 'Test 1' than in 'Test 2'

Thanks

Super User III
Super User III

Re: DAX: Puzzling ALL behaviour in context transition

@AlB

 

When a Table expression returns a single row and a single column..It can be used as a scalar.

 

So for example.You have a simple table (say Table1)

 

Column1

1
1
1

 

Lets define a Calculated Tableas ALL(Table1[Column1])

Since ALL returns unique values in a column/Table, above results in a Table of singlecolumn and single row containing 1

 

So now if you define even a MEASURE as following ..it will work

Measure = ALL(Table1[Column1])

But if you change any one one value in the source table ..it will stop working

1
1
2
 

 

 

 

Try my new Power BI game Cross the River
Super User III
Super User III

Re: DAX: Puzzling ALL behaviour in context transition

Thanks @Zubair_Muhammad

I understand that. My question has more to do with why the filter resulting from context transition has an effect on 'Test 1' but not on 'Test 2'. 

Judging from the result, in 'Test 1' the filter resulting from context transition contains the value of ProductKey in the current row. That seems to override the ALL() to select only one value, a scalar, and it works.  But following that reasoning, 'Test 2' should yield a 1 instead of 2517. as the ALL() is overridden by that one-row filter from context transition. It is that apparently different behaviour what I do not understand. What is your take on that?

Thanks   

 

Microsoft v-danhe-msft
Microsoft

Re: DAX: Puzzling ALL behaviour in context transition

Hi @AlB,

Based on my research, it is due to the simple useage of calculate funtion and the dax engine-The VertiPaq Engine in DAX, when you are using one parameter in calculate function, it takes the existing row contexts (if any) and transforms them into an equivalent filter context. 

You could refer to below link:

https://powerpivotpro.com/2014/03/becoming-one-with-calculate/

https://www.microsoftpressstore.com/articles/article.aspx?p=2449192&seqNum=2

And you could refer to the Chapter 5 Understanding CALCULATE and CALCULATETABLE in the book 

"The Definitive Guide to DAX: Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI"

 

Regards,

Daniel He

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: DAX: Puzzling ALL behaviour in context transition

My recollection was that @Phil_Seamark contacted Jeff Wang about this, and Jeff says it is a bug. Phil, can you confirm?



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Super User III
Super User III

Re: DAX: Puzzling ALL behaviour in context transition

Thanks @v-danhe-msft

That's however what I explained in my previous post and it does not justify the behaviour 

Super User III
Super User III

Re: DAX: Puzzling ALL behaviour in context transition

Thanks very much @MattAllington

Let's see what @Phil_Seamark says then

Microsoft Phil_Seamark
Microsoft

Re: DAX: Puzzling ALL behaviour in context transition

Hi @AlB and @MattAllington

 

I did email Jeffrey Wang about this issue recently and he confirmed it is a bug and that it will be fixed in a future release.  I don't have a timeframe, unfortunately.  


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors