locked
Very Strange CUBEVALUE Problem RRS feed

  • Question

  • Hi everyon,

    I have an extremely weird CUBEVALUE behviour and I am hoping that one of you has seen this and can explain what's going on or maybe even suggest a solution.

    I have a PivotTable connected to a PowerPivot Data model in Excel 2016 version 1806 Build 10228.20080  that looks like this. Super simple stuff:

    

    When I convert the Pivot Table to formulas (Analyze -> OLAP Tools -> Convert to Formulas) and leave the  "Convert Report Filters" Section unticked:

    I get the following result. All fine just like I would expect:

    

    However if I tick the "Convert Report Filters" option, I get the following:

    Suddenly the CUBEVALUE formula returns a blank value!

    I have been working with PowerPivot for a very long time and have never seen this behaviour. Does anyone have an idea of what is going on?

    How can a pivot table show a different value than a cubeformula when the filters are exactly the same?

    The converted formula in expanded form looks like this:

    =CUBEVALUE("ThisWorkbookDataModel","[Measures].[# campaigns]",{"[dCampaignStarts].[MainCG1].&[Lingerie & Beachwear]","[dCampaignStarts].[Customer Segment].&[Premium / Designer Brands]","[dCampaignStarts].[MainBrand].&[EA7 Emporio Armani]"})

    As you can see all filters are on the same table which has a relationship to a fact table where # campaigns is counting the distinct values of the campaign ID column, which is the relationship key.

    Any insights would be highly appreciated.

    Tuesday, July 10, 2018 7:58 AM

Answers

  • An Update! I changed the Formatting of a column in the PowerPivot Model. This triggered a complete refresh of all CUBEVALUE function in my workbook (really annoying behaviour btw). For some strange reason, this caused the calculation to now show the correct value.

    Maybe this is due to some old cache that the CUBEVALUE function was using and only a workbook refresh triggered a true new calculation...

    Tuesday, July 10, 2018 8:10 AM
  • Hi kschaefers,

    Thanks for your question.

    >>>>Maybe this is due to some old cache that the CUBEVALUE function was using and only a workbook refresh triggered a true new calculation...
    I am glad to know that your issue has been resolved.

    I tried to reproduce this issue in my environment as you said, but it seems that everything works fine leave the  "Convert Report Filters" Section ticked or unticked:

    Could you reproduce this issue in another cube or Power Pivot report ?


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, July 11, 2018 5:15 AM

All replies

  • An Update! I changed the Formatting of a column in the PowerPivot Model. This triggered a complete refresh of all CUBEVALUE function in my workbook (really annoying behaviour btw). For some strange reason, this caused the calculation to now show the correct value.

    Maybe this is due to some old cache that the CUBEVALUE function was using and only a workbook refresh triggered a true new calculation...

    Tuesday, July 10, 2018 8:10 AM
  • Hi kschaefers,

    Thanks for your question.

    >>>>Maybe this is due to some old cache that the CUBEVALUE function was using and only a workbook refresh triggered a true new calculation...
    I am glad to know that your issue has been resolved.

    I tried to reproduce this issue in my environment as you said, but it seems that everything works fine leave the  "Convert Report Filters" Section ticked or unticked:

    Could you reproduce this issue in another cube or Power Pivot report ?


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, July 11, 2018 5:15 AM