Using SQL Server 2012 prior to SP1 (any CU), I could run something like the following and get results back. After SP1, I get no data back. (I could use a CROSSJOIN, but that returns invalid results, but at least it returns results. Does anyone know what happened with the GENERATE command after SP1? EVALUATE GENERATE ( SUMMARIZE ( 'Sales Territory' ,'Sales Territory'[Sales Territory Group] ) , SUMMARIZE ( 'Product Category' ,"Internet Sales",SUMX(RELATEDTABLE('Internet Sales'), 'Internet Sales'[Sales Amount]) ) ) Thanks for your help.
I have been doing some analysis of the query plans. Unfortunately, I cannot currently reach my non-SP1 server to verify if what I am seeing is correct. According to BOL, the GENERATE statement works in a fashion similar to a CROSS APPLY in T-SQL. From BOL, Return Value "A Table with the Cartesian product between each row in table1 that results from evaluating table2 in the context of the current row from table1." This implies that some sort of relationship (or relationship chain) must exist between the row in Table1 being evaluated in Table2. Prior to SP1, it appears that the relationship chain would be followed regardless of the depth. Post SP1, I'm not so sure. I will do some testing and see if using RELATEDTABLE, or USERELATIONSHIP functions make a difference. Regardless, it is disconcerting to have this "breaking change" after applying the service packs and cumulative updates.