Home Dashboard Directory Help
Search

A simple DAX query returns unexpected results - PowerPivot v2 11.0.3000.0 by Laurent Couartou


Status: 

Closed
 as Fixed Help for as Fixed


1
1
Sign in
to vote
Type: Bug
ID: 777280
Opened: 1/23/2013 9:41:37 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

Consider the following linked table, called 'SomeData'.

Country    Product    OtherAttribute
USA    A    Y
FRA    B    Y
USA    C    N
FRA    C    N
UK    A    N
GER    B    N
USA    A    N
FRA    B    N

The following query returns the correct results:

EVALUATE CALCULATETABLE(     
    CALCULATETABLE( VALUES('SomeData')
        , ALL('SomeData'[OtherAttribute])     )    
        , FILTER( 'SomeData', [OtherAttribute] = "Y")
     )

These results are the ones I expect: all data for which (Country, Product) exists in FILTER( 'SomeData', [OtherAttribute] = "Y").

The following query returns incorrect results:
EVALUATE CALCULATETABLE(     
    CALCULATETABLE( 'SomeData'
        , ALL('SomeData'[OtherAttribute])     )    
        , FILTER( 'SomeData', [OtherAttribute] = "Y")
     )
This query returns all data for which Product exists in FILTER( 'SomeData', [OtherAttribute] = "Y") - confirmed by SQL Server Profiler. This does not make sense.

Worse, if the order of my columns were Product, Country, OtherAttribute. Then the same query would return different results - all data for which Country exists in FILTER( 'SomeData', [OtherAttribute] = "Y")

Note the only difference in both queries that VALUES('SomeData') was replaced with 'SomeData'.

In other words, modifying only the expression within the innermost CALCULATETABLE statement, changed the way the outer context was calculated.

Details
Sign in to post a comment.
Posted by Microsoft on 5/8/2013 at 12:03 PM
Thank your for reporting this issue. This was indeed a bug in our code, and one of our developers has checked in a fix. The fix will appear in future versions of our product. If you need the fix sooner than that, please ask CSS (Customer Support Services) for a hotfix.

Thanks,
Howie Dickerman
Analysis Services Product Team
Posted by Microsoft on 4/11/2013 at 10:07 AM
Thanks for reporting this issue. One of our developers is investigating.

Thanks,
Howie Dickerman
Analysis Services Product Team
Posted by Laurent Couartou on 2/12/2013 at 3:07 AM
Another way to write the queries, that might clarify what the queries are supposed to do.

EVALUATE CALCULATETABLE(    
     VALUES ('SomeData')
        , // get (Country, Product) tuples for which [OtherAttribute] = "Y"
         SUMMARIZE(
             FILTER( 'SomeData', [OtherAttribute] = "Y")
            , [Country], [Product]
         )
     )

Does not return the same as
EVALUATE CALCULATETABLE(    
     'SomeData'
        , // get (Country, Product) tuples for which [OtherAttribute] = "Y"
         SUMMARIZE(
             FILTER( 'SomeData', [OtherAttribute] = "Y")
            , [Country], [Product]
         )
     )
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
Country_Product_OtherAttribute.xlsx 1/23/2013 126 KB
Product_Country_OtherAttribute.xlsx 1/23/2013 128 KB