A simple DAX query returns unexpected results - PowerPivot v2 11.0.3000.0

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

Resolved

as Fixed

Type:	Bug
ID:	777280
Opened:	1/23/2013 9:41:37 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

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 (expand)

French

SQL Server 2012 - Developer Edition

Analysis Services

Windows 7 Ultimate

English

1. Create the following linked table in Excel 2010 - PowerPivot v2.

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

2. Run the following DAX query against the model:
EVALUATE CALCULATETABLE(
    CALCULATETABLE( 'SomeData'
        , ALL('SomeData'[OtherAttribute])     )
        , FILTER( 'SomeData', [OtherAttribute] = "Y")
     )

SomeData[Country]    SomeData[Product]     SomeData[OtherAttribute]
USA    A    Y
FRA    B    Y
UK    A    N
GER    B    N
USA    A    N
FRA    B    N

SomeData[Country]    SomeData[Product]    SomeData[OtherAttribute]
USA    A    Y
FRA    B    Y
USA    A    N
FRA    B    N

File Name	Submitted By	Submitted On	File Size
Product_Country_OtherAttribute.xlsx	Laurent Couartou	1/23/2013	128 KB
Country_Product_OtherAttribute.xlsx	Laurent Couartou	1/23/2013	126 KB

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]
         )
     )