Automatically list all Powerpivot DAX formulae
Results 1 to 9 of 9

Thread: Automatically list all Powerpivot DAX formulae
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2012
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Automatically list all Powerpivot DAX formulae

    Hi,

    This may seem like a strange question - but what I would like to do is produce as automatically as possible a list of the names of all fields defined in Powerpivot (both in the Powerpivot backend green screen, and anything user defined in the Excel frotn end) that are DAX formulae, and what the formula for that field is. For the fields that are just values i.e. straight from a database , they can either be listed or ignored, it doesn't matter.

    I've seen Excel utilities that do that sort of thing for regular Excel formulae, typically to help audit spreadsheets. But I've not seen how to do that for Powerpivot files - or even to list all the fields enclosed.

    I'd ideally be after a table like:

    Field name Is Formula? DAX formula
    CustomerID No n/a
    TotalSales Yes =SUM([Sales])

    Does that seem possible with either a tool or by developing a macro? I have experience of standard Excel macros but no experience with how much/if any macro functions can interact wtih Powerpivot tables.

    Thanks!

  2. #2
    Board Regular
    Join Date
    Dec 2012
    Posts
    294
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically list all Powerpivot DAX formulae


  3. #3
    Board Regular Jacob Barnett's Avatar
    Join Date
    Dec 2012
    Location
    Manchester, UK
    Posts
    155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically list all Powerpivot DAX formulae

    The simplest way to do it is mentioned in the comments on the post Miguel linked to although you could easily miss it - once you've downloaded the ODC file its 3 clicks every time you want to create the list.

    PowerPivot get all DAX - ODC File

  4. #4
    New Member
    Join Date
    Dec 2012
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically list all Powerpivot DAX formulae

    Hi,

    Happy new year!

    Thanks very much for the responses from you both, very interesting. Unfortunately so far I couldn't get either DAX studio or the ODC technique to work yet in my case.

    Dax studio does not seem to recognise the Powerpivot data source - when I select to use the internal Powerpivot data, the list of databases remains blank.

    With the ODC I get a message to the effect of connection can't be opened when I try to open it in the respective Excel file.

    A bit of googling suggests this might be because I am stuck on Powerpivot v1, which has a different model in some ways. Does that seem likely? I am not able to upgrade to Powerpivot v2 unfortunately due to "corporate rules"...

    From the page linked to above, I saw a link to:
    Guest Post: Catalog Your Measures with a Nifty Macro « PowerPivotPro
    which does seem to work for my workbooks - but it shows only the custom DAX formula defined in the Excel front end (i.e. they appear on the right hand side of Excel with a calculator icon - plus a few others I haven't explicitly defined but seem related to the layout of the pivot table in Excel itself) and misses all the ones defined with a custom DAX formula in the main Powerpivot green screen itself.

    Any further ideas?

    Much appreciated help so far.

  5. #5
    Board Regular
    Join Date
    Dec 2012
    Posts
    294
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically list all Powerpivot DAX formulae

    Hey Adam!

    Happy new year to you too!

    For the DAX Studio issue I believe you're trying to run the add-in with a blank workbook with a powerpivot model on the "background". What you need to do is simple, just create a pivot table (just click the pivot table icon on the powerpivot tab) and once the powerpivot field list appears try running the add-in.

    With the ODC I'm going to let Jacob take this as I really don't have that much experience with the ODC approach.

    With the link, it's the blog prior to the one that I posted before and it's actually not the easiest approach.

    Go for ODC or DAX Studios.

    Hope this helps.
    Miguel

  6. #6
    Board Regular Jacob Barnett's Avatar
    Join Date
    Dec 2012
    Location
    Manchester, UK
    Posts
    155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically list all Powerpivot DAX formulae

    I wasn't aware that the ODC wouldn't work with v1 and having looked at the connection details I'm not entirely sure why it wouldn't - are you sure the .xslx you are in has a PowerPivot model? Otherwise I don't know of any other way to list the DAX for calculated columns automatically.

    The only suggestion I can make is that you need to 'challenge' your corporate rules about getting an upgrade to v2. In my opinion the advantages go way beyond the scope of this topic.

    Jacob
    Last edited by Jacob Barnett; Jan 2nd, 2013 at 06:07 AM.

  7. #7
    New Member
    Join Date
    Dec 2012
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically list all Powerpivot DAX formulae

    Quote Originally Posted by miguel.escobar View Post
    Hey Adam!

    Happy new year to you too!

    For the DAX Studio issue I believe you're trying to run the add-in with a blank workbook with a powerpivot model on the "background". What you need to do is simple, just create a pivot table (just click the pivot table icon on the powerpivot tab) and once the powerpivot field list appears try running the add-in.
    Hi again, thanks for the reply.

    I've tried it being sure to have an active powerpivot pivot table selected in Excel before opening the Studio icon but unfortunately same effect. The DAX studio must recognise it has a powerpivot model in it as when I go to "Change Connection" icon it lets me select "Powerpivot model: Book2.xlsx" for instace. But it does not produce any database name or option to choose it when selected. If I go back into the Change Connection icon it has automatically reselected a (blank) option to use a tabular server instead of the powerpivot.

    The status in the bottom right remains "not connected" always, although no actual error message as such. Likewise the DMV tab, and all the others at the left remain blank.

    The reason I wondered if it was due to v1 was this issue?
    DAX Studio - View Issue #33097: Can't seem to connect to a local PowerPivot model
    where someone mentioned upgrading to v2 was a solution.

    I have tried uninstalling/reinstalling both Powerpivot and the Dax Studio program a few times and rebooting.

    Cheers

    Adam

  8. #8
    New Member
    Join Date
    Dec 2012
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically list all Powerpivot DAX formulae

    Quote Originally Posted by Jacob Barnett View Post
    I wasn't aware that the ODC wouldn't work with v1 and having looked at the connection details I'm not entirely sure why it wouldn't - are you sure the .xslx you are in has a PowerPivot model? Otherwise I don't know of any other way to list the DAX for calculated columns automatically.
    Hi,

    Thanks again - re why it doesn't work, my suspicion is it is because the ODC seems to use this DMV: system.discover_calc_dependency

    This doesn't seem to exist in v1 to me (but definitely could be wrong, I'm not experienced in this!)
    Using a technique like in Querying PowerPivot DMVs from Excel « Chris Webb's BI Blog
    I enumerated all supported DMVs in my Powerpivot file via:select * from $system.discover_schema_rowsets
    and the discover_calc_dependency wasn't there. I'll paste the list of what was there below in case of interest. I look through a few of them but couldn't see anything that stood out yet sadly.



    The only suggestion I can make is that you need to 'challenge' your corporate rules about getting an upgrade to v2. In my opinion the advantages go way beyond the scope of this topic.
    Totally agree This aside, the headline new features of v2 look such an improvement from v1 I can't wait to use it. Sadly today we are dependent on a 2008 sharepoint version which I am told will not support refreshes of v2 models, and I note that you can't backsave v2 powerpivots into v1 as far as I can see (which is logical!). The campaign for a 2012 installation now has that little bit extra ammunition!!

    Thanks again.



    List of supported DMVs in my file:

    SchemaName
    DBSCHEMA_CATALOGS
    DBSCHEMA_TABLES
    DBSCHEMA_COLUMNS
    DBSCHEMA_PROVIDER_TYPES
    MDSCHEMA_CUBES
    MDSCHEMA_DIMENSIONS
    MDSCHEMA_HIERARCHIES
    MDSCHEMA_LEVELS
    MDSCHEMA_MEASURES
    MDSCHEMA_PROPERTIES
    MDSCHEMA_MEMBERS
    MDSCHEMA_FUNCTIONS
    MDSCHEMA_ACTIONS
    MDSCHEMA_SETS
    DISCOVER_INSTANCES
    MDSCHEMA_KPIS
    MDSCHEMA_MEASUREGROUPS
    MDSCHEMA_MEASUREGROUP_DIMENSIONS
    MDSCHEMA_INPUT_DATASOURCES
    DMSCHEMA_MINING_SERVICES
    DMSCHEMA_MINING_SERVICE_PARAMETERS
    DMSCHEMA_MINING_FUNCTIONS
    DMSCHEMA_MINING_MODEL_CONTENT
    DMSCHEMA_MINING_MODEL_XML
    DMSCHEMA_MINING_MODEL_CONTENT_PMML
    DMSCHEMA_MINING_MODELS
    DMSCHEMA_MINING_COLUMNS
    DMSCHEMA_MINING_STRUCTURES
    DMSCHEMA_MINING_STRUCTURE_COLUMNS
    DISCOVER_DATASOURCES
    DISCOVER_PROPERTIES
    DISCOVER_SCHEMA_ROWSETS
    DISCOVER_ENUMERATORS
    DISCOVER_KEYWORDS
    DISCOVER_LITERALS
    DISCOVER_XML_METADATA
    DISCOVER_TRACES
    DISCOVER_TRACE_DEFINITION_PROVIDERINFO
    DISCOVER_TRACE_COLUMNS
    DISCOVER_TRACE_EVENT_CATEGORIES
    DISCOVER_MEMORYUSAGE
    DISCOVER_MEMORYGRANT
    DISCOVER_LOCKS
    DISCOVER_CONNECTIONS
    DISCOVER_SESSIONS
    DISCOVER_JOBS
    DISCOVER_TRANSACTIONS
    DISCOVER_DB_CONNECTIONS
    DISCOVER_MASTER_KEY
    DISCOVER_PERFORMANCE_COUNTERS
    DISCOVER_LOCATIONS
    DISCOVER_PARTITION_DIMENSION_STAT
    DISCOVER_PARTITION_STAT
    DISCOVER_DIMENSION_STAT
    DISCOVER_COMMANDS
    DISCOVER_COMMAND_OBJECTS
    DISCOVER_OBJECT_ACTIVITY
    DISCOVER_OBJECT_MEMORY_USAGE
    DISCOVER_STORAGE_TABLES
    DISCOVER_STORAGE_TABLE_COLUMNS
    DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS

  9. #9
    Board Regular Jacob Barnett's Avatar
    Join Date
    Dec 2012
    Location
    Manchester, UK
    Posts
    155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automatically list all Powerpivot DAX formulae

    You are well outside my sphere of expertise here although I wonder if changing the connection string to use one of the other DMVs might yield results.

    Interesting point about the Sharepoint refresh - sounds like baloney! Not sure Sharepoint 2008 even exists but you need Sharepoint 2010 to run PowerPivot and apart from perhaps upgrading the PowerPivot for sharepoint software there should be no issue.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •