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

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
1
Sign in
to vote
ID 777280 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 1/23/2013 9:41:37 AM
Access Restriction Public

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.

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