List of Calculated Fields formula

[KimC2504]

Hi All,

I have a long list of calculated fields and I would like to check the formula's. Is there a quick way to get a list of all the calculated field names and corresponding formula's on an excel worksheet? Currently I need to view each one individually and copy the formula which is a quite tedious. I am using Excel 2013

TIA
Kim

 

[mfexcel]

Select any cell of the Pivot Table
Go to the PivotTable Tools on the ribbon
Options --> Fields, Items & Set --> List Formulas

This is what you can do in Excel 2010. I assume it is the same in Excel 2013

Hi All,

I have a long list of calculated fields and I would like to check the formula's. Is there a quick way to get a list of all the calculated field names and corresponding formula's on an excel worksheet? Currently I need to view each one individually and copy the formula which is a quite tedious. I am using Excel 2013

TIA
Kim

 

[KimC2504]

Hi Mfexcel, unfortunately that doesn't work in 2013.

 

[prez02]

Hi Mfexcel, unfortunately that doesn't work in 2013.

With Excel 2010, it is possible to create a data connection that queries DMVs of a model, e. g. the query below gives you table with the names and formula of all measures and calc columns.

SELECT DISTINCT OBJECT, EXPRESSION
FROM $system.discover_calc_dependency
WHERE OBJECT_TYPE = 'MEASURE'
OR OBJECT_TYPE = 'CALC_COLUMN'
ORDER BY 1

Once you have created a connection, you can re-use it with any file, which is why I have to admit I pretty much forgot how I created them. And I do not know it this still works in Excel 2013...

 

[KimC2504]

Hi Prez02, I don't understand how this works. Where do you put this query?

 

[prez02]

Hi Prez02, I don't understand how this works. Where do you put this query?

Double-click on a pivot-table to do a drill-through.
Then right-click on the resulting table and select: Table - Edit Query.

You can then replace the command text with the query in my post above.

You can refresh the table, too, when you have changed your calc fields or columns.

If you plan to use this a lot:
Under connections, you'll find a new connection, you can change the name and export it to a different file name into your connections folder. Whenever you need it, you can add the connection and in seconds you have a list of your dax calculations

 

[KimC2504]

I tried that but the edit query was light grey and I couldn't select it

 

[prez02]

I tried that but the edit query was light grey and I couldn't select it

OK, I see, does not work this way in Excel 2013....

<xml id="msodc"><odcfficedataconnection

</odcfficedataconnection
</xml>

 xmlns="http://www.w3.org/TR/REC-html40">


****** http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
****** name=ProgId content=ODC.Table>
****** name=SourceType content=OLEDB>
.PowerPivot Get All DAX statements

Try pasting this into a text file and change .txt to .odc and then copy it into you documents\my datasources or whatever it is called.

Then click on existing connections in the ribbon and double-click on "PowerPivot Get All DAX Statements", which should appear there.

Hope this works...

 

[prez02]

1

 

[KimC2504]

Thankyou Prez02, I will try this tomorrow.