It can be very useful to extract the list of objects created with DAX in a data model, for example for educational and/or project documentation purposes. In this very short article, we will give indications on how to do it, both in the case of a Power Pivot and a Power BI Desktop project.
Both a Power Pivot and a Power BI model are SQL Server Analysis Services Tabular databases. However, the data model in Power Pivot has the severe limitation of not allowing the creation of calculated tables. In this regard, however, linked-back tables should be mentioned, which can constitute an interesting alternative to calculated tables.
Having said that, here is how to behave in both cases:
- Power Pivot model: we suggest using Power Pivot Utilities which include, among other functions, the creation of a new Excel sheet with the list of calculated columns and another with the list of measures. Power Pivot Utilities is an add-in that, once installed, creates a new tab in Excel. In both cases, the list produces the name and code of the objects (figure 1);
- Power BI model: in this case we suggest using DAX Studio, connected to the model, and launching the following query (make sure single quotes are correctly copied) after setting the output to a static Excel file which will be saved wherever you want at the end of the query execution (figures 2 and 3):
SELECT DISTINCT([OBJECT_TYPE]), [OBJECT], [EXPRESSION] from $SYSTEM.DISCOVER_CALC_DEPENDENCY WHERE
[OBJECT_TYPE]=’CALC_COLUMN’ OR [OBJECT_TYPE]=’MEASURE’ OR [OBJECT_TYPE]=’CALC_TABLE’
May DAX be with You!
Autore del Post
