Analysis Services returns different results for the same DAX query depending on the execution

Hernan Demczuk 6 Reputation points
2020-09-23T18:22:00.857+00:00

Depending on the execution, the following query will return different values: 

DEFINE  
    VAR _state_id = 11685   
  
MEASURE accumulators[total_distance_when_number_of_copies_is_1] =  
    CALCULATE(  
         SUM( 'order_details'[distance] )  
         , TREATAS(  
             CALCULATETABLE(  
                 SUMMARIZE(  
                     'order'  
                     , 'order'[city_id]  
                     , 'order'[customer_id]  
                 )  
                 , 'order'[number_of_copies] = 1  
             )    
             , 'order_details'[city_id]  
             , 'order_details'[customer_id]  
         )    
     )  
  
MEASURE accumulators[total_distance_when_number_of_copies_is_2] =   
    CALCULATE(  
         SUM( 'order_details'[distance] )   
         , TREATAS(  
             CALCULATETABLE(  
                 SUMMARIZE(  
                     'order'   
                     , 'order'[city_id]  
                     , 'order'[customer_id]  
                 )  
                 , 'order'[number_of_copies] = 2  
             )     
             , 'order_details'[city_id]  
             , 'order_details'[customer_id]  
         )   
     )  
       
VAR _accumulators = CALCULATETABLE(   
            SUMMARIZECOLUMNS(   
                state[id]   
                , "total_distance_when_number_of_copies_is_1", [total_distance_when_number_of_copies_is_1]   
                , "total_distance_when_number_of_copies_is_2", [total_distance_when_number_of_copies_is_2]   
            )   
    , state[id] = _state_id  
)  
  
EVALUATE _accumulators

If we hit F5 several times this is the result:

27531-sample-gif.gif

As can be seen, the values on the third column sometimes are the same to the ones on the second.

We are running Analysis Services 2019 15.0.32.50 using a tabular model.

It's worth to mention that this is something that doesn't happen on Analysis Services 2017, if we used the same script and backup file, the results for this same query will always be the same no matter how many times we execute it.

Here is the database backup file used in this example: ssas.abf

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,317 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,260 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Lukas Yu -MSFT 5,821 Reputation points
    2020-09-24T04:46:40.093+00:00

    Hi,

    Per my current understanding, the issue is in the VAR _accumulators part. The filter in calculatetable is cause conflict in this part.
    Try change it to : 

    VAR _accumulators = SUMMARIZECOLUMNS(   
                 state[id]   
                 , "total_distance_when_number_of_copies_is_1", [total_distance_when_number_of_copies_is_1]   
                 , "total_distance_when_number_of_copies_is_2", [total_distance_when_number_of_copies_is_2]   
             )

    Regards,
    Lukas

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  2. Alexei Stoyanovsky 3,416 Reputation points
    2020-09-24T07:20:29.167+00:00

    Haven't got a Tabular instance to restore the backup to, but theoretically I see only two options. Either there is some volatility in the database whereby the value for 2 orders is sometimes the same as the value for 1 order. Or, which I find more likely, there's an optimization-related bug in the AS2019 build you're testing.

    0 comments