Forum Discussion

AlB's avatar
AlB
Community Champion
7 years ago
Solved

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]=CALCULA...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    7 years ago

    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.  

v-danhe-msft's avatar
v-danhe-msft
Microsoft Employee
7 years ago

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

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    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

    • v-danhe-msft's avatar
      v-danhe-msft
      Microsoft Employee
      7 years ago

      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

       

      • MattAllington's avatar
        MattAllington
        Community Champion
        7 years ago

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

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      7 years ago

      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
       

       

       

       

      • AlB's avatar
        AlB
        Community Champion
        7 years ago

        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