Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sean
Community Champion
Community Champion

List ALL Calculated COLUMNS in a Data Model

Does anyone know how to get a List of all Calculated COLUMNS in a Data Model?

 

Very easily done if you have a PowerPivot Model using the Power Pivot Utilities - Tool

 

Dax Studio gets me all the Measures (but I'm trying to either get only the COLUMNS or Both Measures and COLUMNS)

 

Steps described here or here don't seem to work...

 

https://docs.microsoft.com/en-us/sql/analysis-services/instances/use-dynamic-management-views-dmvs-t...

 

SELECT * From $SYSTEM.DISCOVER_CALC_DEPENDENCY 
WHERE OBJECT_TYPE = 'MEASURE' OR OBJECT_TYPE = 'CALC_COLUMN'
ORDER BY [EXPRESSION] ASC

This returns a blank table (0 rows)

 

6 REPLIES 6
Phil_Seamark
Employee
Employee

If I run the following

 

select * from $SYSTEM.TMSCHEMA_COLUMNS

I get info on all columns, including formulas used in the [Expression] column which is pretty cool.

 

Perhaps filter to Type = 2?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark  Thanks! Smiley Happy

I wish I could get Table Names instead of Table IDs but I'll take what I can get...

select 
   TableID,
   ExplicitName, 
   Expression

from $SYSTEM.TMSCHEMA_COLUMNS

where [Type] = 2

order by TableID

Maybe @MattAllington could get us another tip from Mike Rudzinski about this like he did here about the Measures

http://exceleratorbi.com.au/getting-started-dax-studio/

select 
   MEASUREGROUP_NAME, 
   MEASURE_NAME, 
   EXPRESSION

from $SYSTEM.MDSCHEMA_MEASURES

where MEASURE_AGGREGATOR = 0

order by MEASUREGROUP_NAME

Also as you see here on @cwebb's blog

https://blog.crossjoin.co.uk/2011/09/17/documenting-dependencies-between-dax-calculations/

and the link he provides

https://msdn.microsoft.com/en-us/library/gg471590(v=sql.110).aspx

select * from $SYSTEM.DISCOVER_CALC_DEPENDENCY

should give us both COLUMNS and Measures - shouldn't it? Or am I missing something?

Top part of picture is from @cwebb's blog and then what I get a table with 0 rows

Discover_Calc_Dependency.png

Thanks! Smiley Happy

Sean
Community Champion
Community Champion

Does anyone know why this works in DAX Studio when connecting to a PowerPivot Model but it does NOT work with a PBI Model?

 SELECT * From $SYSTEM.DISCOVER_CALC_DEPENDENCY 
WHERE OBJECT_TYPE = 'MEASURE' OR OBJECT_TYPE = 'CALC_COLUMN'
ORDER BY [EXPRESSION] ASC

Perhaps @marcorusso can tell us? Smiley Happy  Thanks!

https://docs.microsoft.com/en-us/sql/analysis-services/schema-rowsets/xml/discover-calc-dependency-r...

 

Looking around I also found this interesting Idea by @avisingh that is kind of related.

It will not solve the above issue but would be a nice feature to have!

Measure Dependency View (similar to the Query Dependency View)

 

Thanks!  Smiley Happy

Have you looked at my new Local Host template?  This extrats table data from Power BI Desktop direct into Excel

http://exceleratorbi.com.au/power-bi-desktop-memory-usage/



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Looks like the Table names are in here

 

select * from $SYSTEM.TMSCHEMA_TABLES

I had a look at a model I have and the ID column from that query matches the TableID from the  $SYSTEM.TMSCHEMA_COLUMNS DMV

 

I'm guessing the TM prefix is short for Tabular Model where as DMVs that begin with MD are more for Multidimentional.  So wondering if the TM Tables are more relevant.

 

I just poked around the list of DMV's in the DAX Studio editor, but definitely looks like you can query up some handy info for documentation.   I'm speaking at an SQL Saturday next week so may include this in my presso, because I think its pretty cool.  🙂

 

tn.png

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

So just playing around with this a bit more

 

This seems to give me all calcuated columns including the forumla as well as cool info like the 'sort by column' (leave off the where clause if you want to see/document info about all your columns).

 

select * from $SYSTEM.TMSCHEMA_COLUMNS where [Type]  = 2;

and for calculated measures

 

select * from $SYSTEM.TMSCHEMA_MEASURES

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.