locked
"This formula is invalid" error. RRS feed

  • Question

  • I am trying to follow a tutorial called "Data Analysis Expressions (DAX) In PowerPivot for Excel 2010, v. 1.0" provided by 2010 Microsoft.

    Inside, there is an example on how to deal with inventory count when adding years to column labels.  The measure used is as followed (I renamed the time dimension table and column to match my data):

         

    SUMX(VALUES(DimStore[StoreKey]),  
        SUMX(VALUES(DimProduct[ProductKey]),
        CALCULATE([BaseQty],
        LASTNONBLANK(
        DATESBETWEEN (DimTime[FullDate], BLANK(), LASTDATE(DimTime[FullDate])),
       [BaseQty]))))

    But Powerpivot gave me a "this formula is invalid" error when I tried to include it into the Values pane.  Can anyone tell me what is wrong with this formula?


    BI Analyst

    Monday, April 15, 2013 7:51 PM

Answers

  • After doing some trial and errors I finally found the issue that caused the problem.

    The ""this formula is invalid""comes up when I included date granularity (ex: 1/1/2013) on the Row Labels section of the pivot table.  When I only include Product/Store Name/month, then the formula works fine.

    (The example can be found in page 46 of the Data Analysis Expressions (DAX) In PowerPivot for Excel 2010.docx.)


    BI Analyst

    • Marked as answer by BIAnalyst Thursday, April 18, 2013 6:37 PM
    Thursday, April 18, 2013 6:36 PM

All replies

  • How is the referenced [BaseQty] measure defined?


    Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com

    Monday, April 15, 2013 8:05 PM
    Answerer
  • Thank you for your prompt response, Brent. The measure is defined as followed:

    BaseQty:= SUM(FactInventory[OnHandQuantity])

    (This example comes from Data Analysis Expressions in PowerPivot for Excel 2010.docx.  It can be downloaded from Microsoft page, but I cannot see to find the link. If you search for the name on the Web, you will find many links.)


    BI Analyst

    Monday, April 15, 2013 8:26 PM
  • After doing some trial and errors I finally found the issue that caused the problem.

    The ""this formula is invalid""comes up when I included date granularity (ex: 1/1/2013) on the Row Labels section of the pivot table.  When I only include Product/Store Name/month, then the formula works fine.

    (The example can be found in page 46 of the Data Analysis Expressions (DAX) In PowerPivot for Excel 2010.docx.)


    BI Analyst

    • Marked as answer by BIAnalyst Thursday, April 18, 2013 6:37 PM
    Thursday, April 18, 2013 6:36 PM