locked
creating new measures in bulk? RRS feed

  • Question

  • I have about 50 measures and have been creating them one by one. Very tedious. I want to create new measures by loading them in bulk.  Is there a way to load them with a script or other tool?
    Wednesday, February 1, 2012 8:41 AM

Answers

  • Currently there is no API in PowerPivot so you can't script or code against it.    But I'm curious.. what kind of measures are you creating and why do you need 50?  if you are creating measure that could be automatically generated in the source query (In a stored procedure using dynamic SQL, for example) you may be able to save some time...


    Javier Guillen
    http://javierguillen.wordpress.com/
    • Proposed as answer by Challen Fu Wednesday, February 8, 2012 8:01 AM
    • Marked as answer by Challen Fu Thursday, February 9, 2012 8:47 AM
    Wednesday, February 1, 2012 8:38 PM
    Answerer

All replies

  • Currently there is no API in PowerPivot so you can't script or code against it.    But I'm curious.. what kind of measures are you creating and why do you need 50?  if you are creating measure that could be automatically generated in the source query (In a stored procedure using dynamic SQL, for example) you may be able to save some time...


    Javier Guillen
    http://javierguillen.wordpress.com/
    • Proposed as answer by Challen Fu Wednesday, February 8, 2012 8:01 AM
    • Marked as answer by Challen Fu Thursday, February 9, 2012 8:47 AM
    Wednesday, February 1, 2012 8:38 PM
    Answerer
  • I am trying to create new measures in the PowerPivot field list in Excel. The 50 measures are for various performance metrics - the reason there are so many is that they are specific to a company division.
    Thursday, February 2, 2012 5:25 AM
  • There are techniques that in some scenarios can allow a DAX expression to evaluate dynamically.  I am wondering if you could leverage this and have the formula evaluate dynamically for each company division.   Can you give a small example of what you data looks like and what the expected output should be?


    Javier Guillen
    http://javierguillen.wordpress.com/
    Thursday, February 2, 2012 10:53 AM
    Answerer
  • Javier has one good idea.

    Example: a dollar metric in one division and a euro in another. With a division slicer then use an if statement, if div 1, divide x/y and format as $ else use w/z and format as EU. Also the switch verb can change the entire formula based on a match, a+b, a+c, etc when the division matches.

    Another alternative is get PPutils from SQLBI (free) which will extract all your column/measures into a tab on your workbook. You can then use that to copy/paste them all into a new workbook without the typing as much. Still tedious but not as hard as retyping the whole equations.

    Friday, December 2, 2016 5:19 PM
  • Hello pugmeat,

    There is a solution for this in SSAS environment. You can load your DAX measures in bulk and even tweak them before loading. You need to download an add-on for your visual studio called DAX Editor. Here is the link. https://marketplace.visualstudio.com/items?itemName=DAXEditor.DAXEditor

    Imke Feldmann  from TheBIccountant.com put a great video together how the whole process is working.
    Here is the link: www.youtube.com/watch?v=pvLABnRgkZY

    Good Luck

    • Proposed as answer by Nick Chobotar Sunday, December 4, 2016 2:42 AM
    Sunday, December 4, 2016 2:42 AM