none
Named Set Issue RRS feed

  • Question

  • Shortly after PowerPivot CTP3 shipped, I decided to experiment with Named Sets. Because the sets Excel creates aren't flexible, I decided to create one using MDX. Alas, my very first script crashed Excel. I'm wondering if anyone else has had a similar experience or can verify my problem.

    Using the AdventureWorks DW database, I created a set using the following script:
    TOPCOUNT({([DimProductSubcategory].[EnglishProductSubcategoryName].[EnglishProductSubcategoryName])},5,[Measures].[Sum of SalesAmount])

    I can't recall whether Excel crashed after I created the set or when I later tried to add it to the row area of a PivotTable.

    Anyway, I created a calculated measure [SalesTotals]=SUM('FactInternetSales'[SalesAmount]) and redid the set as:
    TOPCOUNT({([DimProductSubcategory].[EnglishProductSubcategoryName].[EnglishProductSubcategoryName])},5,[Measures].[SalesTotals])

    And it worked fine.  One thing that's not clear is why ORDER, when added to the script, doesn't work i.e. I can't get my top count ordered from highest to lowest - I have to do the ordering manually in the PivotTable (same issue when creating a set from an AS cube).
    Wednesday, December 30, 2009 6:12 PM

Answers

  • Hi, Colin,

    I did the following test and did not reproduce the crash.
    1) Open Excel
    2) Import FactAllSales, DimProduct, DimProductSubcategory, DimProductCategory into PowerPivot
    3) Open PivotTable, drag SalesAmount to Values field
    4) Create named set using your code
    TOPCOUNT({([DimProductSubcategory].[EnglishProductSubcategoryName].[EnglishProductSubcategoryName])},5,[Measures].[Sum of SalesAmount])
    5) Refresh to see the new set, add the new set to the Row Labels field. It returned the correct result.
    6) I also tried removing SalesAmount from the Values, and dragged other numeric column, such as TaxAmount to the Values field, it also worked.

    In your test, could you reproduce the problem each time, or it was one time occurance? If it was the latter, do you mind testing it again and let me know how it goes?

    Thanks!
    Lisa
    • Proposed as answer by Rob Collie Saturday, January 2, 2010 5:35 AM
    • Marked as answer by snappvlModerator Monday, April 19, 2010 8:14 PM
    Thursday, December 31, 2009 4:11 PM
    Moderator

All replies

  • Hi, Colin,

    Can you clarify whether [Measures].[Sum of SalesAmount] is a DAX calculated measure, if so, can you share how [Sum of SalesAmount] is defined?

    Regarding the order in the result, the MDX TopCount function does return result in the descending order on the numeric value when a numeric expression is passed as the third argument. It appears Excel rearranges the result set and sorts the first (left most) column in the dictionary order. Experts from Excel team may shed some light on this behavior.

    Thanks,
    Lisa
    Wednesday, December 30, 2009 10:09 PM
    Moderator
  • Hi Lisa,

    No. I believe that [Measures].[Sum of SalesAmount] is used internally by the AS engine. For example, GETPIVOTDATA uses [Measures].[Sum of SalesAmount] when you point to a SalesAmount value cell. It might be valid only when SalesAmount is in the PivotTable. If so, MDX should provide better handling for this scenario. As I recall, "Test MDX" didn't report an error. 
    Wednesday, December 30, 2009 10:49 PM
  • Hi, Colin,

    I did the following test and did not reproduce the crash.
    1) Open Excel
    2) Import FactAllSales, DimProduct, DimProductSubcategory, DimProductCategory into PowerPivot
    3) Open PivotTable, drag SalesAmount to Values field
    4) Create named set using your code
    TOPCOUNT({([DimProductSubcategory].[EnglishProductSubcategoryName].[EnglishProductSubcategoryName])},5,[Measures].[Sum of SalesAmount])
    5) Refresh to see the new set, add the new set to the Row Labels field. It returned the correct result.
    6) I also tried removing SalesAmount from the Values, and dragged other numeric column, such as TaxAmount to the Values field, it also worked.

    In your test, could you reproduce the problem each time, or it was one time occurance? If it was the latter, do you mind testing it again and let me know how it goes?

    Thanks!
    Lisa
    • Proposed as answer by Rob Collie Saturday, January 2, 2010 5:35 AM
    • Marked as answer by snappvlModerator Monday, April 19, 2010 8:14 PM
    Thursday, December 31, 2009 4:11 PM
    Moderator
  • Hi Lisa,

    It seems that I can't reproduce the problem. Thanks, and sorry for the trouble.

    Colin
    Thursday, December 31, 2009 5:37 PM
  • No problem at all, Colin. Have fun with PowerPivot and Happy New Year!

    Lisa
    Thursday, December 31, 2009 8:17 PM
    Moderator
  • Thank you. Wishing you and the rest of the team a very Happy New Year!
    Thursday, December 31, 2009 9:13 PM